On 2/4/08, Piotr Budny <[EMAIL PROTECTED]> wrote: > Hello, > I've got strange behaviour, I want to select row ID of MAX(something) row for > group: > > CREATE TABLE "test" ( "id" INTEGER , "name" CHAR(50) , "weight" INTEGER ); > INSERT INTO "test" VALUES(1,'A',1); > INSERT INTO "test" VALUES(2,'A',50); > INSERT INTO "test" VALUES(3,'B',1); > INSERT INTO "test" VALUES(4,'C',35); > INSERT INTO "test" VALUES(10,'C',2); > INSERT INTO "test" VALUES(15,'C',123); > > 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. Now: > delete from test; > INSERT INTO "test" VALUES(15,'C',123); > INSERT INTO "test" VALUES(1,'A',1); > INSERT INTO "test" VALUES(2,'A',50); > INSERT INTO "test" VALUES(3,'B',1); > INSERT INTO "test" VALUES(4,'C',35); > INSERT INTO "test" VALUES(10,'C',2); > > (the id=15 is now on the top) > > sqlite> select id,name,max(weight) from test group by name; > 2|A|50 > 3|B|1 > 10|C|123 > > No way, the id=10 is C, but not "123". > What's wrong? Isn't it a bug? > Tried this on MySQL with same data. MySQL works fine. >
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> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
