retrieving the record which has only one value

Question

Hello All,

Please suggest me a query, which retrieves only those record which has the single row in table. For example:

**table1.**
name age
aaa  20
bbb  10
ccc  20
ddd  30

If I run "select distinct age from table1. result will be:

age
20
10
30

But I need a query, which give the result like

name age
bbb  10
ddd  30

I am using DB2 as database. Thanks....

Answer 1

To get just the unique ages:

SELECT age
FROM table1
GROUP BY age
HAVING COUNT(*) = 1

To also get the name column, in MySQL you would just add the column:

SELECT name, age
FROM table1
GROUP BY age
HAVING COUNT(*) = 1

This trick won't work in most other databases though, and I'm guessing that it won't work in DB2. Instead you can use an aggregate function like MIN or an inner select with an EXISTS, IN or JOIN:

SELECT MIN(name), age
FROM table1
GROUP BY age
HAVING COUNT(age) = 1

or:

SELECT name, age
FROM table1
WHERE age IN (
    SELECT age
    FROM table1
    GROUP BY age
    HAVING COUNT(age) = 1
)

Answer 2

SELECT name, age
FROM table1
GROUP BY age
HAVING COUNT(age)=1