Thanks Ryan As often is the case the the actual problem is more complex than my example - sometimes we over simplify to, well, simplify - but you have both given me some ideas and I'll go away and play.
Paul Paul www.sandersonforensics.com SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> On 29 June 2018 at 20:24, R Smith <ryansmit...@gmail.com> wrote: > The solution from David works perfectly, just want to point out some CTE > things since you mention getting into it. > 1st - A nice thing about CTE is that, in the case of a non-recursive CTE > (like this one), it can well be replaced by a simple sub-query, however, > the CTE can be referenced more than once, unlike a sub-query. > 2nd - One can even use the CTE in other sub-queries. > > With this in mind, here is another option for the query which is > functionally equivalent (i.e. it's not better, simply showing alternate CTE > use): > > with status_one as ( > select * > from names > where status = 1 > ) > select min(id), status, name > from names > where status = 0 and name not in (select name from status_one) > group by status, name > union all > select * from status_one > ; > > > CTE capability is one of my favourite additions ever to SQLite (I may have > mentioned this before), so I hope you too find them useful and joyful. > > Cheers, > Ryan > > > On 2018/06/29 6:45 PM, David Raymond 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
