On Fri, Oct 28, 2011 at 06:42:06PM +0200, Tobias Sj??sten scratched on the wall: > I have a table: > > CREATE TABLE t > ( > i INT, > g VARCHAR(1), > v INT > );
> 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. What are you expecting? The output of the GROUP BY (which includes an arbitrary "v" value for each group, since you didn't define a grouping method for "v") is sorted by "v". In this case, 3 == 3, so there isn't any meaning in the order. Remember that GROUP BY is applied *before* ORDER BY. > 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. MySQL would likely throw an error with this query. Most DBs will not not let you have "v" in the results without being in the GROUP BY clause and/or using it as a parameter in an aggregate function. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
