CODEWITHSUNDEEP
databasedb2
user306689
user306689

Reputation: 145

retrieving the record which has only one value

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....

Upvotes: 1

Views: 45

Answers (2)

Mark Byers
Mark Byers

Reputation: 839114

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
)

Upvotes: 1

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 799430

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

Upvotes: 0

Related Questions