how to select first non-duplicate data in sql

Question

I have table(Id, Name, Type) in sql.

Id, Name, Type:
1, AA, 1
2, BB, 2
3, CC, 4
4, DD, 2
5, EE, 3
6, FF, 3

I want select the first non-duplicate data. Result:

Id, Name, Type:
1, AA, 1
2, BB, 2
3, CC, 4
6, FF, 3

I use DISTINCT and GROUP BY, but not working, I have select all row not select Type with DISTINCT or GROUP BY.

select DISTINCT Type
from tbltest

So you want to remove duplicate `Type`s and first means according to the ID? — Tim Schmelter, Aug 12 '14 at 12:18

score 2 · Accepted Answer · answered Aug 12 '14 at 12:16

2

I like CTE's and ROW_NUMBER since it allows to change it easily to delete the duplicates.

Presuming that you want to remove duplicate Types and first means according to the ID:

WITH CTE AS(
   SELECT Id, Name, Type,
       RN = ROW_NUMBER() OVER ( PARTITION BY Type ORDER BY ID )
   FROM dbo.Table1
)
SELECT Id, Name, Type FROM CTE WHERE RN = 1

answered Aug 12 '14 at 12:16

Tim Schmelter

450,073
74
686
939

@user3933292: yes, it is efficient unless the data is not extremely large (so billions of records). http://stackoverflow.com/a/2309247/284240 Of course you need to use proper indexes. – Tim Schmelter Aug 12 '14 at 12:29

Gordon Linoff · Answer 2 · 2014-08-12T12:22:50.873

You can do this in several ways. My preference is row_number():

select id, name, type
from (select t.*, row_number() over (partition by type order by id) as seqnum
      from tbltest t
     ) t
where seqnum = 1;

EDIT:

Performance of the above should be reasonable. However, the following might be faster with an index on type, id:

selct id, name, type
from tbltest t
where not exists (select 1 from tbltest t2 where t2.type = t.type and t2.id < t.id);

That is, select the rows that have no lower id for the same type.

how to select first non-duplicate data in sql

2 Answers2