On Thu, Apr 25, 2013 at 10:29:34AM +0200, Hick Gunter scratched on the wall: > AFAIK SQLite treats GROUP BY the same way as ORDER BY (taken from hints > in the virtual table description).
They're not the same clause, they don't do the same thing. Now, it is true that most database systems implement the first step of a GROUP BY by sorting the query using semantics that are similar to ORDER BY. That way all of the rows in a related group are next to each other, and they're easier to process. I assume SQLite does the same thing. It is, however, as they say, "an implementation detail." > IF you have an index that covers the GROUP BY clause in any other order, > then you still have the guarantee that all rows belonging to the same > group will be retrieved together, but the result rows will be ordered > in index order and not GROUP BY order. Except there is no such thing as "GROUP BY order". SQL Golden Rule: If there is no ORDER BY, the rows have no order. According to SQL, neither the groups, nor the rows within a group (as they are fed into aggregates) have a defined order. Any query that makes assumptions about the ordered result of a GROUP BY is broken. Use the out-of-order index. -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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
