Thank You David - I was just starting to play with CTEs
Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> On 29 June 2018 at 17:45, David Raymond <david.raym...@tomtom.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
