CODEWITHSUNDEEP
sqldatabase
MrPedru22
MrPedru22

Reputation: 1344

SQL select records with unique ID

I have a table with information concerning patients and their diagnostics. Each patient has a unique identifier, given by NUM_SEQUENCIAL and can have multiple diagnosis. I have extracted the information from a larger table, with the following query:

select DISTINCT 
    (NUM_SEQUENCIAL), COD_DIAGNOSTICO 
FROM 
    PCE_RP.DIAGNOSTICOS_DOENTE 
WHERE 
    COD_DIAGNOSTICO = '153' OR 
    COD_DIAGNOSTICO = '1530' OR 
    COD_DIAGNOSTICO = '1531' OR
    COD_DIAGNOSTICO = '1532' OR 
    COD_DIAGNOSTICO = '1533' OR 
    COD_DIAGNOSTICO = '1534' OR 
    COD_DIAGNOSTICO = '1536' OR 
    COD_DIAGNOSTICO = '1537' OR 
    COD_DIAGNOSTICO = '1538' OR 
    COD_DIAGNOSTICO = '1539' OR 
    COD_DIAGNOSTICO = '1540' OR 
    COD_DIAGNOSTICO = '1541';

The result was the following TABLE:

NUM_SEQUENCIAL | COD_DIAGNOSTICO
2              | 1541
3              | 153
3              | 1533
4              | 1536
4              | 153

Question: how to select only unique patient records, with a given COD_DIAGNOSTICO? It can be any COD_DIAGNOSTICO, I just want to obtain a table like the following, for instance:

NUM_SEQUENCIAL | COD_DIAGNOSTICO
2              | 1541
3              | 153
4              | 1536

Thanks in advance.

Upvotes: 0

Views: 954

Answers (2)

Petko Kostov
Petko Kostov

Reputation: 367

Try

SELECT NUM_SEQUENCIAL, COD_DIAGNOSTICO
FROM PCE_RP.DIAGNOSTICOS_DOENTE 
GROUP BY NUM_SEQUENCIAL
HAVING COUNT(NUM_SEQUENCIAL) = 1

Upvotes: 0

Greg Viers
Greg Viers

Reputation: 3523

I find MAX and MIN both work for this. Max is better because it will never choose empty values.

SELECT  
    (NUM_SEQUENCIAL), MAX(COD_DIAGNOSTICO ) COD_DIAGNOSTICO
FROM 
    PCE_RP.DIAGNOSTICOS_DOENTE 
WHERE 
    COD_DIAGNOSTICO = '153' OR 
    COD_DIAGNOSTICO = '1530' OR 
    COD_DIAGNOSTICO = '1531' OR
    COD_DIAGNOSTICO = '1532' OR 
    COD_DIAGNOSTICO = '1533' OR 
    COD_DIAGNOSTICO = '1534' OR 
    COD_DIAGNOSTICO = '1536' OR 
    COD_DIAGNOSTICO = '1537' OR 
    COD_DIAGNOSTICO = '1538' OR 
    COD_DIAGNOSTICO = '1539' OR 
    COD_DIAGNOSTICO = '1540' OR 
    COD_DIAGNOSTICO = '1541'; 

GROUP BY NUM_SEQUENCIAL

You should probably use IN () instead of all those ORs. But I left them in for now.

Upvotes: 3

Related Questions