This is documented behaviour for SQLite: SELECT a, MAX(b) table;
Will return (one of) the a value(s) that comes from the same row as the MAX(b). If there are not exactly on of MIN or MAX aggregate functions, SQLite is free to pick any row (within a group) to return non-aggregated columns from. Thus: Select a,SUM(b), c from table group by a; will return one of the c values from each group of a values. The same thing applies for DISTINCT. -----Urspr?ngliche Nachricht----- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Scott Robison Gesendet: Montag, 09. Mai 2016 08:13 An: SQLite mailing list Betreff: Re: [sqlite] SELECT DISTINCT question On Sun, May 8, 2016 at 7:45 PM, dandl <david at andl.org> wrote: > Just to add to the below: > > S# | SNAME | STATUS | CITY > ------------------------------- > S1 | Smith | 20 | London > S2 | Jones | 10 | Paris > S3 | Blake | 30 | Paris > S4 | Clark | 20 | London > S5 | Adams | 30 | Athens > > SELECT DISTINCT "CITY" FROM "S" GROUP BY "CITY" ORDER BY "S#" ASC ; > > CITY > ------ > Paris > London > Athens > > I don't find it easy to explain this result. > My guess based on the available data is that, since you don't specify which "S#" you want associated with each city, it is picking the max of each (coincidentally). If you want the minimum S# value, this seems to work: select distinct city from s group by city order by min("S#") asc; I'm not sure if that *should* work per "standard" SQL, but it does with SQLite. I'd have expected something like this to be necessary: select city, min("S#") as x from s group by city order by x asc; And if you only want the city: select city from (select city, min("S#") as x from s group by city order by x asc); But I'm not a SQL master. Distinct used with group by seems redundant, but again, I might just not understand how they are useful together. -- Scott Robison _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
