CODEWITHSUNDEEP
mysqlselectunique
Martin
Martin

Reputation:

Select only unique rows in mysql

I want to select only rows that are unique according to the column userid from a mysql table. So if I have two rows with the same userid none of them gets selected but if its only one it gets selected.

I use the following code:

SELECT T.id,T.name,T.userid FROM tbltest T WHERE userid NOT IN (SELECT userid FROM tbltest WHERE tbltest.id<>T.id);

Is this the fastest way to do it?

Thanks!

Upvotes: 2

Views: 10583

Answers (3)

Chris J
Chris J

Reputation: 1375

Try this:

SELECT T.id, T.name, T.userid, count(*) AS count FROM tbltest GROUP BY T.userid HAVING count = 1;

Upvotes: 3

Hawk Kroeger
Hawk Kroeger

Reputation: 2334

How is this

SELECT T.id,T.name,T.userid 
FROM tbltest T 
GROUP BY T.userid
HAVING COUNT(T.userid) = 1;

Upvotes: 11

Greg
Greg

Reputation: 321806

I don't think that's the fastest way... you could try:

SELECT T.id, T.name, T.userid, COUNT(*) AS num 
FROM tbltest T GROUP BY T.userid HAVING num = 1

Or

SELECT T.id, T.name, T.userid, COUNT(*) AS num FROM tbltest T
WHERE NOT EXISTS
(SELECT 1 FROM tbltest T2 WHERE T2.userid = T.userid AND T2.id != T.id)

Upvotes: 1

Related Questions