How do I obtain for each distinct primary key occurrence in a table, the first of an ordered set of rows?
I want to obtain a specific set of results from a table in a single query and I'm not being able to find how to do it.
I've a got a table recording every agent state change (online, offline, away, etc) and the time when it happened.
I want to obtain for each agent id the last state change, i.e. considering agents A and B, I'd have the original table containing:
ID | STATE | DATETIME
A | Online | 29-11-2010
A | Offline | 30-11-2010
B | Away | 28-11-2010
B | Onl开发者_运维问答ine | 30-11-2010
And I would want to generate an output like so:
ID | STATE | DATETIME
A | Offline | 30-11-2010
B | Online | 30-11-2010
I realize this might be a really basic select query, but I can't put my finger on what my be. Also mad props if it could be INFORMIX compatible :D
How about:
SELECT datetime, id, state FROM table t1
WHERE t1.datetime = (SELECT MAX(t2.datetime) FROM table t2 WHERE t2.id=t1.id)
精彩评论