with status_one as ( select * from names where status = 1 ), one_names as ( select distinct name from status_one ) select min(id), status, name from names where status = 0 and name not in one_names group by status, name
union all select * from status_one; -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Paul Sanderson Sent: Friday, June 29, 2018 11:50 AM To: General Discussion of SQLite Database Subject: [sqlite] unique values from a subset of data based on two fields I have a table Create table names (id int, status int, name text) 1, 1, 'paul' 2, 1,'helen' 3, 0, 'steve' 4, 0, 'steve' 5, 0, 'pete' 6, 0, 'paul' I want a query that returns all of the records with status = 1 and unique records, based on name, where the status =0 and the name is not in the list status=1 So from the above I would want to see 1, 1, paul 2, 1, helen 3, 0, steve (or 4, 0, steve) 5, 0, pete I could do something like Select * from names where status = 1 or name not in (select name from names where status = 1) But this gets both rows for steve, e.g. 1, 1, paul 2, 1, helen 3, 0, steve 4, 0, steve 5, 0, pete while I am not bothered about which of the two steves I get back, I must have all occurences of names with status = 1 I am probably missing somethng obvious Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
