Igor, Did you get my response? I thought of something that you should try, which is adding a where clause.
SELECT timestamp, col1, min(col2) FROM table WHERE col2=min(col2) <----- Here GROUP BY col1 ORDER BY min(col2) ASC This is why I am thinking you should use the where clause: The min() is just giving you the smallest value. It is not moving your record pointer to the corresponding timestamp. Grouping is just pulling the first instance where it finds a unique value for col1. The "where," I am thinking, will filter your results to make sure that the returned set will include whichever record where col2 = the min(col2). -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Igor Tandetnik Sent: Wednesday, December 14, 2011 7:36 PM To: [email protected] Subject: Re: [sqlite] Returning other columns of an aggregate result On 12/14/2011 8:21 PM, Dilip Ranganathan wrote: > 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? select b.timestamp, a.col1, b.col2 from (select distinct col1 from myTable) a join myTable b on b.rowid = (select rowid from myTable where col1 = a.col1 order by col2 limit 1) order by b.col2; -- Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
