On 2/4/08, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > P Kishor <[EMAIL PROTECTED]> wrote: > > On 2/4/08, Piotr Budny > > <[EMAIL PROTECTED]> wrote: > >> Now, I want to get the ID for such max rows: > >> > >> select id,name,max(weight) from test group by name; > > > > sqlite> select t.* from test t join (select name, max(weight) as m > > from test group by name) w on t.weight = w.m and t.name = w.name; > > 15|C|123 > > 2|A|50 > > 3|B|1 > > sqlite> > > Beware of ties (two or more rows in the same group sharing the largest > weight). >
right, in which case Max(weight) will return two values, both being max. sqlite> select * from test; 15|C|123 1|A|1 2|A|50 3|B|1 4|C|35 10|C|2 sqlite> INSERT INTO "test" VALUES(18,'C',123); sqlite> select t.* from test t join (select name, max(weight) as m from test group by name) w on t.weight = w.m and t.name = w.name; 15|C|123 2|A|50 3|B|1 18|C|123 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
