开发者

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)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜