CODEWITHSUNDEEP
mysqlsqlselectcount
Elmi
Elmi

Reputation: 6213

Select a value from MySQL database only in case it exists only once

Lets say I have a MySQL table that has the following entries:

1
2
3
2
5
6
7
6
6
8

When I do an "SELECT * ..." I get back all the entries. But I want to get back only these entries, that exist only once within the table. Means the rows with the values 2 (exists two times) and 6 (exists three times) have to be dropped completely out of my result.

I found a keyword DISTINCT but as far as I understood it only avoids entries are shown twice, it does not filters them completely.

I think it can be done somehow with COUNT, but all I tried was not really successful. So what is the correct SQL statement here?

Edit: to clarify that, the result I want to get back is

1
3
5
7
8

Upvotes: 2

Views: 8429

Answers (4)

James Pegg
James Pegg

Reputation: 191

You want to mix GROUP BY and COUNT().

Assuming the column is called 'id' and the table is called 'table', the following statement will work:

SELECT * FROM `table` GROUP BY id HAVING COUNT(id) = 1

This will filter out duplicate results entirely (e.g. it'll take out your 2's and 6's)

Upvotes: 6

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

Three ways. One with GROUP BY and HAVING:

SELECT columnX
FROM tableX
GROUP BY columnX
HAVING COUNT(*) = 1 ;

one with a correlated NOT EXISTS subquery:

SELECT columnX
FROM tableX AS t
WHERE NOT EXISTS
      ( SELECT *
        FROM tableX AS t2
        WHERE t2.columnX = t.columnX
          AND t2.pk <> t.pk             -- pk is the primary key of the table
      ) ;

and an improvement on the first way (if you have a primary key pk column and an index on (columnX, pk):

SELECT columnX
FROM tableX
GROUP BY columnX
HAVING MIN(pk) = MAX(pk) ;

Upvotes: 2

TJ-
TJ-

Reputation: 14373

select id from foo group by id having count(*) < 2;

Upvotes: 1

Sirko
Sirko

Reputation: 74086

You can use COUNT() in combination with a GROUP BY and a HAVING clause like this:

SELECT yourCol
FROM yourTable
GROUP BY yourCol
HAVING COUNT(*) < 2

Example fiddle.

Upvotes: 6

Related Questions