SELECT timestamp, col1, min(col2) FROM table GROUP BY col1 ORDER BY min(col2) ASC
The min() is just giving you the smallest value. It is not moving your record pointer to the corresponding timestamp. You will have to do that by yourself or specify better what the purpose of your logic is. That's why you get grouping like you did. It is giving you the first record where col1 meets the criteria and reporting the min value of col2 across the set. You will note that the timestamp you are getting back is simply the first instance of each of the groups (col1). -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dilip Ranganathan Sent: Wednesday, December 14, 2011 7:21 PM To: General Discussion of SQLite Database Subject: [sqlite] Returning other columns of an aggregate result I am not an expert in SQL, so do bear with me if I am asking the obvious. Given: timestamp | col1 | col2 ============================ xx abc 5 yy abc 4 zz def 7 rr def 6 SELECT timestamp, col1, min(col2) FROM table GROUP BY col1 ORDER BY min(col2) ASC returns: xx abc 4 zz def 6 It looks like the timestamp column is kind of random since it is not part of the group by clause. Suppose I want the results to be: yy abc 4 rr def 6 what kind of SQL would I have to write? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
