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. Regards, vip _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
