On 13-01-2012 12:07, Dilip Ranganathan 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:
Because it only works 'to an extent', try: select t1.person, max(t1.value), t2.timestamp from test t1 left join test t2 on t1.person=t2.person and t.1value=t2.value group by t1.person; -- Luuk _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
