A very minor change on your query is all that is needed. select max(x.timestamp),x.value,x.person from table as x, (select person,max(value) as maxvalue from table group by person order by maxvalue desc) as y where x.person = y.person and x.value = y.maxvalue group by x.person, x.value
I don't know how this compares in terms of performance with Igor's solution. --David Garfield On Fri, Jan 13, 2012 at 06:07, Dilip Ranganathan <misc.us...@gmail.com>wrote: > I have a table that looks like something like this: > > timestamp value person > =============================================== > 2010-01-12 00:00:00 33 emp1 > 2010-01-12 11:00:00 22 emp1 > 2010-01-12 09:00:00 16 emp2 > 2010-01-12 08:00:00 16 emp2 > 2010-01-12 12:12:00 45 emp3 > 2010-01-12 13:44:00 64 emp4 > 2010-01-12 06:00:00 33 emp1 > 2010-01-12 15:00:00 12 emp5 > > I wanted to find the maximum value associated with each person. The obvious > query was: > > select person,max(value) from table group by person > > Now I wanted to include the timestamp associated with each max(value). I > could not use timestamp column in the above query because as everyone > knows, it won't appear in the group by clause. So I wrote this instead: > > select x.timestamp,x.value,x.person from table as x, > (select person,max(value) as maxvalue from table group by person order by > maxvalue desc) as y > where x.person = y.person > and x.value = y.maxvalue > > This works -- to an extent. I now see: > > timestamp value person > =============================================== > 2010-01-12 13:44:00 64 emp4 > 2010-01-12 12:12:00 45 emp3 > 2010-01-12 06:00:00 33 emp1 > 2010-01-12 00:00:00 33 emp1 > 2010-01-12 08:00:00 16 emp2 > 2010-01-12 09:00:00 16 emp2 > 2010-01-12 15:00:00 12 emp5 > > The problem is now I get all the entries for emp1 and emp2 that ends up > with the same max(value). > > Suppose among emp1 and emp2, I only want to see the entry with the latest > timestamp. IOW, I want this: > > timestamp value person > =============================================== > 2010-01-12 13:44:00 64 emp4 > 2010-01-12 12:12:00 45 emp3 > 2010-01-12 06:00:00 33 emp1 > 2010-01-12 09:00:00 16 emp2 > 2010-01-12 15:00:00 12 emp5 > > What kind of query would I have to write? Is it possible to extend the > nested query I wrote to achieve what I want or does one have to rewrite > everything from the scratch? > > If its important, timestamps are actually stored as julian days. I use the > datetime() function to convert them back to a string representation in > every query. > _______________________________________________ > 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