Note that this is SQLite3 specific (and specific to Sybase of the era where Microsoft SQL Server was actually just a rebranded Sybase, and Microsoft re-writes of SQL Server up to about 2000). Technically you cannot do a query of the form:
SELECT c1, c2 FROM t1 GROUP BY c2; because each column in the select list must be either an aggregate or listed in the GROUP BY clause. SQLite3 allows c1 to be a bare column however and the value returned is taken from "some random row" of the group. If there are multiple such columns, they all come from the same row in the group. Although documented as a "random" row of the group, it is the first (or last) row visited in the group while solving the query (and this is of course subject to change but within the same version of SQLite3 will deterministically be the row either first or last in the visitation order -- the actual row may of course change depending on use of indexes, etc). You can re-write this part so it will work in other SQL dialects that strictly enforce the requirement for c1 to be either an aggregate or listed in the group by clause. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Paul Sanderson >Sent: Saturday, 30 June, 2018 04:18 >To: SQLite mailing list >Subject: Re: [sqlite] unique values from a subset of data based on >two fields > >Easier and pretty obvious :) Thanks Keith > > > >Paul >www.sandersonforensics.com >SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074> > >On 29 June 2018 at 23:20, Keith Medcalf <kmedc...@dessus.com> wrote: > >> >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 >> >> Translation into SQL using English to SQL Translator, using the >most >> direct translation on the "problem statement" above directly into >SQL: >> >> create table names (id int, status int, name text); >> insert into names values (1, 1, 'paul'); >> insert into names values (2, 1, 'helen'); >> insert into names values (3, 0, 'steve'); >> insert into names values (4, 0, 'steve'); >> insert into names values (5, 0, 'pete'); >> insert into names values (6, 0, 'paul'); >> >> -- I want a query that returns all of the records with status = 1 >> >> SELECT id, >> status, >> name >> FROM names >> WHERE status == 1 >> >> -- and >> >> UNION >> >> -- unique records, based on name, where the status = 0 and the name >is not >> in the list [of names where] status=1 >> >> SELECT id, >> status, >> name >> FROM names >> WHERE status == 0 >> AND name NOT IN (SELECT name >> FROM names >> WHERE status == 1) >> GROUP BY name; >> >> Returns the rows: >> >> 1|1|paul >> 2|1|helen >> 3|0|steve >> 5|0|pete >> >> If the table is bigger than trivial (ie, contains more than the >number of >> rows you can count with your fingers) then you will need the >appropriate >> indexes to achieve performant results. >> >> --- >> The fact that there's a Highway to Hell but only a Stairway to >Heaven says >> a lot about anticipated traffic volume. >> >> >> >-----Original Message----- >> >From: sqlite-users [mailto:sqlite-users- >> >boun...@mailinglists.sqlite.org] On Behalf Of Paul Sanderson >> >Sent: Friday, 29 June, 2018 09:50 >> >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
