Piotr Budny <[EMAIL PROTECTED]> wrote: > select name,max(weight) from test group by name; > > The result is fine: > A|50 > B|1 > C|123 > > Now, I want to get the ID for such max rows: > select id,name,max(weight) from test group by name; > > 2|A|50 > 3|B|1 > 15|C|123 > > It is OK.
It's only OK by accident. The id comes from some row in each group, but there's no guarantee that it comes from the same row that MAX(weight) comes from. In general, it is impossible to match up a field not mentioned in either GROUP BY or an aggregate with the row on which MIN or MAX is achieved. Consider: select id, name, min(weight), max(weight) from test group by name; Which id would you expect to see in the result? Also, in the original query, what id do you expect to see if there are two rows with the same name and the same weight that is highest in their group? In fact, many SQL engines consider such a query to be invalid: they insist that any field should either be inside an aggregate, or mentioned in GROUP BY clause. > Tried this on MySQL with same data. MySQL works fine. You mean, happens to work fine for a particular set of data and a particular order of insertion. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
