CODEWITHSUNDEEP
sqlsqlite
Dev01
Dev01

Reputation: 4222

getting unique results without using group by

For some reason, I am not able to use GROUP BY but I can use DISTINCT like this:

SELECT DISTINCT(name), id, email from myTable

However above query lists people with same name also because I am selecting more than one columns whereas I want to select only unique names. Is there someway to get unique names without using GROUP BY ?

Upvotes: 1

Views: 41

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726579

Although using GROUP BY is the most direct way, you can do other things if it is prohibited. For example, you can use NOT EXISTS with a subquery, like this:

SELECT name, id, email
FROM myTable t
WHERE NOT EXISTS (SELECT * FROM myTable tt WHERE tt.name=t.name AND tt.id < t.id)

This query uses NOT EXISTS to eliminate rows with the same name and IDs higher than the one selected. Note that since this query must pick a single user per name, it may eliminate some users based on their ID, which is a rather arbitrary criterion.

Upvotes: 2

Related Questions