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.
精彩评论