Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    26

    Unanswered: TSQL : how to delete duplicated rows except the top 1 order by some fields?

    the table is like :

    ID F1 F2 F3
    --- --- --- ---
    1 A 1 VR
    2 B 2 VR
    3 A 3 VF
    4 A 2 VF
    5 B 1 VF
    ......

    the rules is:
    if there are rows with same values of F3 field,then choose the top 1 row order by F1,F2 in the group,and delete other rows.

    how can I delete rows with ID in (2 , 3 , 5)?
    ......

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You sure you don't mean 2,4,5?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    drop table #tmp
    create table #tmp(id int,f1 char(1),f2 int,f3 char(2))
    go
    insert #tmp select 1, 'A', 1, 'VR'
    insert #tmp select 2, 'B', 2, 'VR'
    insert #tmp select 3, 'A', 3, 'VF'
    insert #tmp select 4, 'A', 2, 'VF'
    insert #tmp select 5, 'B', 1, 'VF'
    go
    select *
    --delete
    from #tmp t
    where id not in(select top 1 t2.id from #tmp t2 where t2.f3=t.f3 order by f1,f2)
    and f3 in(select f3 from #tmp group by f3 having count(*)>1)

  4. #4
    Join Date
    Jan 2004
    Posts
    26
    sure! because row[A,2] will on top of row[A,3] if they are order by (F1,F2)
    ......

  5. #5
    Join Date
    Jan 2004
    Posts
    26
    Originally posted by snail
    drop table #tmp
    create table #tmp(id int,f1 char(1),f2 int,f3 char(2))
    go
    insert #tmp select 1, 'A', 1, 'VR'
    insert #tmp select 2, 'B', 2, 'VR'
    insert #tmp select 3, 'A', 3, 'VF'
    insert #tmp select 4, 'A', 2, 'VF'
    insert #tmp select 5, 'B', 1, 'VF'
    go
    select *
    --delete
    from #tmp t
    where id not in(select top 1 t2.id from #tmp t2 where t2.f3=t.f3 order by f1,f2)
    and f3 in(select f3 from #tmp group by f3 having count(*)>1)
    it works! thanks!
    ......

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I thought I could get away with just coorelation...but alas

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99([ID] int, F1 char(1), F2 int, F3 char(2))
    GO
    
    INSERT INTO myTable99([ID], F1, F2, F3) 
    SELECT 1, 'A', 1, 'VR' UNION ALL
    SELECT 2, 'B', 2, 'VR' UNION ALL
    SELECT 3, 'A', 3, 'VF' UNION ALL
    SELECT 4, 'A', 2, 'VF' UNION ALL
    SELECT 5, 'B', 1, 'VF'
    GO
    
    SELECT *
      FROM myTable99 o 
     WHERE o.F1+CONVERT(char(1),o.F2) 
        IN  (SELECT TOP 1 i.F1+CONVERT(char(1),i.F2) 
    	   FROM myTable99 i
    	  WHERE i.F3 = o.F3
           ORDER BY F1,F2
    )
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    DO a SHOWPLAN...

    The 2 queries do the opposite of each other...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2004
    Posts
    26
    yes!
    and you also note me that we don't need condition of 'f3 in(select f3 from #tmp group by f3 having count(*)>1) ' in where clause.
    ......

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •