your problem is that v refers to data in the original row-- which v you see when you group is totally arbitrary, and there's no requirement on sql to use the same one for ordering. You need to specify an aggregate on the non-grouping columns to really have any sort of defined behavior.
What I would suggest is something like: select g,min(v) as val from t group by g order by val asc; -sean -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Tobias Sjösten Sent: Friday, October 28, 2011 9:42 AM To: [email protected] Subject: [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 ----------------------------------------------------------------------------------- This email message is for the sole use of the intended recipient(s) and may contain confidential information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. ----------------------------------------------------------------------------------- _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
