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
