CODEWITHSUNDEEP
mysqlsql
Narayan
Narayan

Reputation: 169

How to select only the unique records

By that I mean leaving any duplicate records

For example

ID  NAME
1   a
2   a
3   b
4   b
5   c

Desired output.

5 c only

I am tired of trying this . So I don't think I don't have any reasonably code to paste here .

Upvotes: 0

Views: 60

Answers (5)

sudeep
sudeep

Reputation: 414

Here is yet another way to do it:

SELECT * FROM table AS A 
   WHERE (SELECT COUNT(*) FROM table AS T 
            WHERE T.NAME = A.NAME) = 1

Upvotes: 0

AdamMc331
AdamMc331

Reputation: 16691

I think the cleanest way to do this is to select the name and id for each row, group by the name, and filter only on values that have a COUNT(*) of 1. This means any rows that have a name that is not unique are excluded.

It would look like this:

SELECT id, name
FROM myTable
GROUP BY name
HAVING COUNT(*) = 1;

I can't get SQL to work, but verified this in MySQL workbench:

enter image description here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Here is one way:

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

Here is another way:

select t.*
from table t join
     (select name, count(*) as cnt
      from table t
      group by name
      having cnt = 1
     ) tt
     on tt.name = t.name;

Upvotes: 1

Uncle Iroh
Uncle Iroh

Reputation: 6045

select a.* 
from table a
left join table b on a.name = b.name and a.id <> b.id
where b.id is null;

Upvotes: -1

Ruslan
Ruslan

Reputation: 2797

Here is another possible way:

select min(id), name from table group by name having count(*) = 1

Upvotes: 1

Related Questions