Select unique records in sqlite

Question

Consider a table

results (id, key, value) where key is the Primary key.

Sample data:

id | key | value ----------------------- abc| source-1 | 20 abc| source-2 | 30 abc| source-3 | 2 abc| source-4 | 10 def| source-5 | 1 ghi| source-6 | 25 jkl| source-5 | 13

I would like to return only those records which have a single entry for a given id. So output should be

id | key | value ------------------------ def| source-5 | 1 ghi| source-6 | 25 jkl| source-5 | 13

Please advise.

How can key be the primary key, if there are duplicate values (source-5)? — beny23
– beny23, Commented Sep 27, 2012 at 2:21

mu is too short · Accepted Answer · 2012-10-04 05:01:09Z

4

One way is to use GROUP BY and HAVING to produce a derived table with the desired ids and then JOIN to it:

select results.* from results join ( select id from results group by id having count(*) = 1 ) as dt on results.id = dt.id

You could also use an IN if you don't like derived tables:

select * from results where id in ( select id from results group by id having count(*) = 1 )

edited Oct 4, 2012 at 5:01

answered Sep 27, 2012 at 2:17

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Bartholomew Over a year ago

Is the sub-query actually required here? Testing with the data from the question SQLite3 gives me the right result just for SELECT id, key, value FROM t GROUP BY id HAVING count(*) == 1;. My SQL-fu isn't strong enough to know if that will always work though.

mu is too short Over a year ago

@JohnBartholomew: Not for SQLite (and MySQL) but yes for most other databases. If you don't use a derived table to produce just the ids you want, a lot of databases will complain about selecting unaggregated and ungrouped columns. Things like select a, b from t group by a are ambiguous (which b should you choose for duplicate as?) so many databases complain, some just quietly grab a b. Most databases would complain about the == too.

Collectives™ on Stack Overflow

Select unique records in sqlite

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related