You must Sign In to post a response.
  • Category: SQL Server

    How to delete distinct rows in SQL

    Hi

    I have a table like below i want to delete the distinct rows in that table.
    Ex: i want to delete the rows of 33,34,36.

    ID Name Date

    19 Gamma 2014-07-14 15:57:35.577
    33 Beta 2014-07-20 09:05:27.100
    34 Beta 2014-07-25 09:05:35.820
    35 Beta 2014-07-28 09:05:40.887
    36 Alpha 2014-07-20 09:06:07.697
    37 Alpha 2014-07-25 09:06:13.647
  • #748199
    I see that all the rows in the data mentioned are distinct except for the name column value all other values are distinct. So Do you want to delete the rows based on the duplication of names or any other column?
    Miss. Jain
    Microsoft Certified Technology Specialist in .Net

  • #748201
    Use the below scripts to delete distinct rows

    DELETE
    FROM dbo.temp
    WHERE id NOT IN (SELECT MAX(id)
    FROM dbo.temp
    GROUP BY name)



    WITH cte
    AS (SELECT *,
    RN = ROW_NUMBER()
    OVER(
    partition BY name
    ORDER BY id DESC)
    FROM dbo.temp)
    DELETE
    FROM cte
    WHERE rn > 1

  • #748203
    WITH CTE AS(
    SELECT ID,Name ,Date ,
    RN = ROW_NUMBER()OVER(PARTITION BY Name ORDER BY Name)
    FROM tblName
    )
    DELETE FROM CTE WHERE RN > 1

  • #748207
    Hey,

    Please be clear. With which parameter you want to perform this operation. Because if you will go with ID all the rows will get deleted. Meanwhile, if you want to delete the duplicate rows from the table, the below query may help you-

    DELETE FROM table_name A WHERE ROWID > (
    SELECT min(rowid) FROM table_name B
    WHERE A.key_values = B.key_values);

    Thanks,
    Ashutosh Jha
    http://tricksroad.com

  • #748208
    Use the Query as



    DELETE
    FROM dbo.tableName

    WHERE id NOT IN
    (
    SELECT Distinct id

    FROM dbo.tablename

    GROUP BY ID
    )



    Thanks & Regards
    Anil Kumar Pandey
    Microsoft MVP, DNS MVM

  • #748211
    Hi,

    I cannot see any row which has got same data except for the column "Name". Can you tell me the criteria you would like to use to delete the data clearly.

    Don't except the delete statement until you are clear on what you supposed to do as your will be loosing the important data which you cannot recover.


    Regards,
    Asheej T K

  • #748218
    Hi,

    If you want to delete duplicate records in a table then refer below sample query. This will help you to clear your doubts.


    declare @tab table
    (
    ID INT,
    value int
    )

    insert into @tab
    values(1,1),(2,2),(3,1),(4,2),(5,1),(6,3)

    SELECT * FROM @tab

    ;WITH TempTable (VALUE,DuplicateRecordCount)
    AS
    (
    SELECT VALUE,ROW_NUMBER() OVER(PARTITION by VALUE ORDER BY VALUE) AS DuplicateRecordCount
    FROM @tab
    )

    DELETE FROM TempTable
    WHERE DuplicateRecordCount > 1

    SELECT * FROM @tab


    Try something like above to achieve your goal..

    Hope this will help you...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #748221
    I don't see any relation between these rows, if you see basically there is no logical relation between records 33,34,36 why do you want to delete it, how can you say that these records are distinct
    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]


  • Sign In to post your comments