I think this is (or ought to be) an FAQ.
v is considered to be random and unreliable -- you only want one record and you're asking it to pick one-from-N without any logic (you assume order by does this but it still returns a set and not a single value). I don't know if mysql will give you a different answer for ascending or descending but that's probably coincidental. I don't think this is considered standard behavior (though I could quite likely be wrong about that). What you want is this: select g,min(v) from t group by g; It's explicit and will give you the same answer on all implementations. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________ From: [email protected] [[email protected]] on behalf of Tobias Sjösten [[email protected]] Sent: Friday, October 28, 2011 11:42 AM To: [email protected] Subject: EXT :[sqlite] ORDER BY disregarded for GROUP BY I have a table: CREATE TABLE t ( i INT, g VARCHAR(1), v INT ); And the data: INSERT INTO t (i,g,v) VALUES (1,'a',2); INSERT INTO t (i,g,v) VALUES (1,'a',1); INSERT INTO t (i,g,v) VALUES (1,'a',3); INSERT INTO t (i,g,v) VALUES (1,'b',2); INSERT INTO t (i,g,v) VALUES (1,'b',1); INSERT INTO t (i,g,v) VALUES (1,'b',3); Now when I select that data, ordered by 'v', it shows correctly: > SELECT g,v FROM t ORDER BY v ASC; a|1 b|1 a|2 b|2 a|3 b|3 But when I group it by 'g' it completely disregards the ordering: > SELECT g,v FROM t GROUP BY g ORDER BY v ASC; a|3 b|3 Using descending order does not matter, btw. The result is the same. I'm not sure if this is the expected behavior in SQLite but for me it certainly wasn't. I was thinking SQLite would act as MySQL and respect the ordering when grouping. Is there a way around this or is it a bug with SQLite? -- tobiassjosten.net // +46 (0) 70 - 091 17 55 // twitter.com/tobiassjosten _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
