remove dup rows within same columns

  • err..i missed the other part of join eh..

    this one works i suppose

    delete x from

    ( select row_number() OVER (order by getdate()) as id,col1,col2 from #t1 ) x,

    ( select row_number() OVER (order by getdate()) as id,col1,col2 from #t1) Y

    where x.col2 = y.col1 and y.col2=x.col1 and x.id > y.id

  • Here's an sql solution to leave one row from a pair of 'opposites'.

    create table #t1 (A varchar(10), B varchar(10))

    insert into #t1(A,B) select 'john', 'sainz'

    insert into #t1(A,B) select 'john','candy'

    insert into #t1(A,B) select 'irfan','khan'

    insert into #t1(A,B) select 'sainz', 'john'

    insert into #t1(A,B) select 'candy','john'

    insert into #t1(A,B) select 'travolta', 'carlos'

    -- These are the rows left in the table after eliminating 1 row of

    -- 2 rows of opposite values.

    WITH TEMP AS (SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS ID,A,B

    FROM #T1)

    SELECT *

    FROM TEMP

    WHERE ID NOT IN

    (

    SELECT XID

    FROM

    (SELECT X.ID AS XID,Y.ID AS YID

    FROM TEMP AS X INNER JOIN TEMP AS Y

    ON X.A=Y.B AND X.B=Y.A AND X.ID>Y.ID) AS C

    )

    ORDER BY ID

    ID A B

    -------------------- ---------- ----------

    1 john sainz

    2 john candy

    3 irfan khan

    6 travolta carlos

    -- Query to delete a row of opposites.

    WITH TEMP AS (SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS ID,A,B

    FROM #T1)

    DELETE FROM E FROM #T1 AS E

    WHERE

    EXISTS

    (

    SELECT A,B

    FROM

    (SELECT A,B

    FROM TEMP

    WHERE ID IN

    (

    SELECT XID

    FROM

    (

    SELECT X.ID AS XID,Y.ID AS YID

    FROM TEMP AS X INNER JOIN TEMP AS Y

    ON X.A=Y.B AND X.B=Y.A AND X.ID>Y.ID) AS C

    )

    ) AS F

    /* These are rows in F (from TEMP) that we want to delete from #T1

    A B

    ---------- ----------

    sainz john

    candy john

    */

    WHERE E.A=F.A AND E.B=F.B )

    SELECT * FROM #T1

    A B

    ---------- ----------

    john sainz

    john candy

    irfan khan

    travolta carlos

    In general this situation should never occur in a table. I would handle this with a constraint as explained here:

    http://beyondsql.blogspot.com/2008/07/opposite-constraint.html

    www.beyondsql.blogspot.com

  • USE Northwind

    GO

    CREATE TABLE [Employee]

    (

    [ID] [int] NOT NULL ,

    [Name] [nvarchar] (10) NOT NULL ,

    [Salary] [money] NULL ,

    )

    GO

    INSERT INTO Employee VALUES

    (1, 'Dinesh', 1500)

    (1, 'Dinesh', 25000)

    (1, 'Dinesh', 15000)

    SELECT * FROM Employee ORDER BY 1

    /*******************************************************************/

    /* Script for Deletion of Duplicate Record from the Table */

    GO

    DECLARE @id INT,

    @name VARCHAR (50),

    @cnt INT,

    @salary NUMERIC

    DECLARE GetAllRecords CURSOR LOCAL STATIC FOR

    SELECT COUNT(1), ID, NAME, salary

    FROM Employee WITH (NOLOCK)

    GROUP BY ID, NAME, salary

    HAVING COUNT(1) > 1

    OPEN GetAllRecords

    FETCH NEXT FROM GetAllRecords INTO @cnt, @id, @name, @salary

    --Cursor to check with all other records

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @cnt = @cnt - 1

    SET rowcount @cnt

    -- Deleting the duplicate records. Observe that all fields are mentioned at the where condition

    DELETE FROM Employee WHERE ID = @id AND NAME = @name AND salary = @salary

    SET rowcount 0

    FETCH NEXT FROM GetAllRecords INTO @cnt, @id, @name, @salary

    END

    CLOSE GetAllRecords

    DEALLOCATE GetAllRecords

    /*******************************************************************/

    GO

    DROP TABLE Employee

    GO

  • The problem discussed on this topic is different from what you have explained.

    And btw, Why you need to use a cursor when you can do this with a query 😉

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply