On 2/4/08, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > 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 > >
The general rule I follow (a paraphrase of what Igor is saying) -- use only one aggregate function in the SELECT clause. then take all other columns in the SELECT clause and repeat them in the GROUP BY clause. If that doesn't give you what you want, then rework your query. SELECT col1, col2... AggreateFunction(coln)... FROM table GROUP BY col1, col2... (all columns except coln) WHERE if required _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
