CODEWITHSUNDEEP
sqldatabaseplsql
Gonçalo Borges
Gonçalo Borges

Reputation: 49

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;

Upvotes: 0

Views: 145

Answers (5)

reiver
reiver

Reputation: 117

All you have to do, is group by column1 and then filter your results with the "having" clause, just like this:

WITH MY_TABLE AS(
  SELECT 'A' AS COLUMN1, 1234 AS COLUMN2 FROM DUAL UNION ALL
  SELECT 'A' AS COLUMN1, 6666 AS COLUMN2 FROM DUAL UNION ALL
  SELECT 'A' AS COLUMN1, 7777 AS COLUMN2 FROM DUAL UNION ALL
  SELECT 'B' AS COLUMN1, 1234 AS COLUMN2 FROM DUAL UNION ALL
  SELECT 'B' AS COLUMN1, 6666 AS COLUMN2 FROM DUAL UNION ALL
  SELECT 'C' AS COLUMN1, 6666 AS COLUMN2 FROM DUAL UNION ALL
  SELECT 'D' AS COLUMN1, 1234 AS COLUMN2 FROM DUAL
)
SELECT    COLUMN1 
FROM      MY_TABLE
GROUP BY  COLUMN1
HAVING    COUNT(COLUMN1) < 2;

Upvotes: 0

apomene
apomene

Reputation: 14389

Try with distinct: 

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

Upvotes: 1

Ravi
Ravi

Reputation: 31407

The column list in select should match with column list in GROUP BY, You should not use * when you are specifying individual columns in GROUP BY or else you need to specify all the columns in group by clause.

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

Upvotes: 1

NikNik
NikNik

Reputation: 2301

Your query is fine, but you can't select all columns (only that appear in group by clause unless...). So you need max(column2) that is in any case unique :

SELECT columnNeeded, max(column2)
FROM myTable
GROUP BY columnNeeded
HAVING COUNT(*) = 1;

Upvotes: 1

Matt
Matt

Reputation: 15061

Use a CTE.

;WITH yourtable AS
(SELECT *, COUNT(*) OVER (PARTITION BY column1) AS col1count FROM mytable)
SELECT column1, column2
FROM yourtable
WHERE col1count = 1

Output

column1 column2
C       6666
D       1234

SQL Fiddle: http://sqlfiddle.com/#!15/2fd7f/1/0

Upvotes: 0

Related Questions