Search code examples
mysqlsqlduplicatesredundancy

SQL Select Unique Rows

Example1:

IDENT | CURRENT | SOURCE
12345 | 12345   | A
23456 | 12345   | B
34567 | 12345   | C

Example2:

IDENT | CURRENT | SOURCE
56789 | 56789   | A

Table with 3 columns, IDENT, CURRENT and SOURCE. I'm trying to write a query that will only display * on records where there are only one unique CURRENT record and IDENT = CURRENT (EXAMPLE 2). There are records that have the same CURRENT but different IDENT (Example 1), these records should be omitted from the results.

All current queries I'm trying where IDENT=CURRENT is displaying results similar to EXAMPLE 1. Not sure if I need somehow use WHERE CURRENT COUNT = 1.

Solution 
  • select * from table 
where ident=current 
and current in (select current from table group by 1 having count(*)=1)

    or without a subquery

    select 
min(ident) as ident, 
current, min(source) as source
from table 

where ident=current 
group by current
having count(*)=1