开发者

MySQL - query help select results with latest timestamp grouped by date

I have a table containing the following fields: date, time, node, result describing some numeric result for different nodes at different dates and times throughout each day. Typical listing will look something like this:

date       | time  | node | result
---------开发者_StackOverflow社区-------------------------
2011-03-01 | 10:02 | A    | 10
2011-03-01 | 11:02 | A    | 20
2011-03-02 | 03:13 | A    | 23
2011-03-02 | 12:15 | A    | 18
2011-03-02 | 13:15 | A    | 8
2011-03-01 | 13:12 | B    | 2
2011-03-01 | 14:26 | B    | 1
2011-03-02 | 08:00 | B    | 6
2011-03-02 | 07:22 | B    | 3
2011-03-02 | 21:19 | B    | 4

I want to form a query that'll get the last result from each day for each node, such that I'd get something like this:

date       | time  | node | latest
-----------------------------------
2011-03-01 | 11:02 | A    | 20
2011-03-01 | 14:26 | B    | 1
2011-03-02 | 13:15 | A    | 8
2011-03-02 | 21:19 | B    | 4

I thought about doing a group by date, node, but then extracting the last value was a mess (I used group_concat( result order by time ) and used SUBSTRING() to get the last value. Baah, I know). Is there a simple way to do this in mysql?


I'm pretty sure I saw a similar request solving it very nice without using an INNER JOIN but I can't find it right now (and it might have been SQL Server) but following should work nevertheless.

SELECT n.*
FROM   Nodes n
       INNER JOIN (
         SELECT MAX(time) AS Time
                , Date
                , Node
         FROM   Nodes
         GROUP BY
                Date
                , Node
       ) nm ON nm.time = n.time
               AND nm.Date = n.Date
               AND nm.Node = n.Node


I would think that you would have to use something like the Max() function. Sorry I don't have mysql, so I can't test but I would think something like this

select t.date, t.node, t.latest, Max(time) from Table t Group By t.node, t.date

I think the aggregate function will return only the one row per grouping.

0

上一篇:

下一篇:

精彩评论

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

最新问答

问答排行榜