Gonçalo Borges Gonçalo Borges - 3 years ago 213
SQL Question

query that returns the values from column1 that occur only one time

Let's have a table like this:

|---------------------|------------------|
| column1 | column2 |
|---------------------|------------------|
| A | 1234 |
| A | 6666 |
| A | 7777 |
| B | 1234 |
| B | 6666 |
| C | 6666 |
| D | 1234 |
|---------------------|------------------|


I want a query that returns C and D.

I want a query that returns the values from column1 that occur less than 2 times (edited: after I have "only one time").

I am using plSQL.

I have tried this:

SELECT *
FROM myTable
GROUP BY columnNeeded
HAVING COUNT(*) < 2;

Answer Source

The column list should match with GROUP BY, You should not use * when specifying one column in GROUP BY.

SELECT column1 
FROM myTable
GROUP BY column1
HAVING COUNT(column1) < 2;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download