On Wed, Dec 14, 2011 at 8:35 PM, Igor Tandetnik <itandet...@mvps.org> wrote:
> 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 Thanks. Along the same lines, I have another question. I have a table like this: timestamp | category | col1 | col2 ======================== xx CAT1 3 5 yy CAT1 5 6 zz CAT3 2 9 rr CAT3 4 3 I wanted to find the latest entry for every category based on their timestamp. The records in the table are always inserted in such a way that the last record always has the latest timestamp. In my first pass, I mistakenly wrote the query like this: (assume timestamp is stored as julian days) select timestamp, category, col, col2 from table group by category order by timestamp desc This query doesn't make sense since after the grouping I am not specifying anywhere which record is be chosen out of the grouped lot. Yet the result was like this: yy CAT1 5 6 rr CAT3 4 3 Is it that from the grouped records, sqlite automatically chooses the latest inserted record? _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
