CODEWITHSUNDEEP
sqlselectdb2
macunte
macunte

Reputation: 465

Select individual records only

I have a table as such

custno, type, color
A1234, B, Red
A1234, C, Blue
A1277, B, Red
A1288, A, Black
A1288, B, Red
A1289, A, Black

I need to retrieve only the unique records A1277 and A1289 that are only found once.

Upvotes: 0

Views: 85

Answers (3)

Pravin Dwiwedi
Pravin Dwiwedi

Reputation: 21

Try this as this will prevent costly join operation--

SELECT custno,type,color FROM ( SELECT custno, type, color, COUNT(custno) OVER(PARTITION BY custno) CNT FROM tableName )TMP WHERE CNT=1

Upvotes: 0

Sirko
Sirko

Reputation: 74036

SELECT custno
FROM yourTable
GROUP BY custno
HAVING COUNT(*) = 1

Example Fiddle

Upvotes: 1

John Woo
John Woo

Reputation: 263703

This will display the custNO on the result list,

SELECT  custNo
FROM    tableName
GROUP   BY custNO
HAVING  COUNT(*) = 1

but if you want to get the whole row,

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  custNo
            FROM    tableName
            GROUP   BY custNO
            HAVING  COUNT(*) = 1
        ) b ON a.custNo = b.custNo

Upvotes: 2

Related Questions