CODEWITHSUNDEEP
mysqlsqldatabase
Usama Saeed
Usama Saeed

Reputation: 25

How to avoid all records which has a duplicate present and get only unique records?

Hy mates,

I have a database table which looks like this:

uniqueId,asin,rank
1,abc,1
2,xyz,2
3,abc,1
4,xyz,2
5,opq,3

As you can see that the asin's (abc and xyz) were repeated. So I would like my query to avoid them completey and return me only (opq).

Best Regards

Usama

Upvotes: 1

Views: 31

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270291

not exists should have the best performance:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.asin = t.asin and t2.id <> t.id
                 );

For performance, you want an index on (asin, id).

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

I think you need 

select *
from yourtable a
where 1 = (
  select count(*)
  from yourtable
  where a.asin = asin
)

Demo

Upvotes: 2

Related Questions