MYSQL Query: latest timestamp + unique value from the last 30 minutes

Question

I need to retrieve the latest rows with a unique value from my mysql table. Simple table layout is timestamp (now()) and a username column. The table gets new data a couple of times a second, and i need the latest row where username is unique.

SELECT MAX(timestamp) as timestamp, username 
    FROM bla 
    WHERE timestamp < (now() - interval 30 minute) 
    GROUP BY username 
    ORDER BY timestamp DESC

It seems that this query does not return the latest values, probably because the group is doing something i dont want...

So basically you want the most recent row for each user that was inserted within the last 30 minutes? Also, be aware that `timestamp` is a reserved word within mysql. You should be using a different name for the field, or "escaping" it with backticks. — Marc B, Feb 10 '11 at 19:18
Yes you are correct, my column uses another name for the timestamp value this was just an example :) thanks for the tip anyway! — HyperDevil, Feb 10 '11 at 19:26

score 6 · Answer 1 · answered Feb 10 '11 at 19:17

6

If you're trying to look at the last 30 minutes, then I think you want to use "greater than" rather than "less than".

... WHERE timestamp > (now() - interval 30 minute) ...

answered Feb 10 '11 at 19:17

Joe Stefanelli

132,803
19
237
235

+1 Was just about to click Submit. Nice - you type faster. ;-) – Ken White Feb 10 '11 at 19:18
Oops yes i saw that one, but the result is still not showing the latest values. – HyperDevil Feb 10 '11 at 19:19
@HyperDevil: If rows are inserted at a rate of 2/second, it's probably going to be tough to validate the results of the query against live data. – Joe Stefanelli Feb 10 '11 at 19:21
I definitely see your point, but i can see that there are newer results in the table before i run the query. – HyperDevil Feb 10 '11 at 19:22
@HyperDevil: Is your ``timestamp`` column actually a timestamp datatype? I wonder if there's some issue with the conversion to/from UTC? – Joe Stefanelli Feb 10 '11 at 19:40
it is of type: timestamp NOT datetime – HyperDevil Feb 10 '11 at 19:42
I think it really does work now, but it is really the "correct" way to do this query? It there a way of doing this query faster? – HyperDevil Feb 10 '11 at 20:19
@HyperDevil: I suppose technically you don't need the `WHERE` clause at all. The `max(timestamp)` is going to be the same whether you're looking at all rows or only those from the last 30 minutes. – Joe Stefanelli Feb 10 '11 at 20:34
I see your point! The table is very large, maybe the where is constricting the MAX() search? Or is it already doing a max() on the whole column ? – HyperDevil Feb 11 '11 at 08:19

score 3 · Answer 2 · edited May 23 '17 at 11:53

While what you wrote does work, a faster solution might be to use a LEFT JOIN On the DB I'm working on LEFT JOIN is actually twice as fast w/ the added bonus that you get all the columns from the most recent rows if you want that info:

SELECT *
    FROM `bla`
    WHERE bla.created_at > (now() - interval 30 minute) AND (next_bla.created_at IS NULL)
    LEFT JOIN bla next_bla ON bla.username = next_bla.username AND bla.created_at < next_bla.created_at
    ORDER BY bla.created_at DESC

this matches every row in bla w/ the next row by timestamp w/ the same username and picks the rows that don't have a next row (next_bla.created_at IS NULL) i.e. they are the most recent rows.

You can also use a LIMIT clause to improve performance.

This is a good article that explains how GROUP BY works in detail and clear language: http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html

Another similar answer by Andomar - MySQL "Group By" and "Order By"

score 2 · Accepted Answer · edited Sep 12 '12 at 16:09

2

SELECT MAX(timestamp) as timestamp, username 
    FROM bla 
    WHERE timestamp > (now() - interval 30 minute) 
    GROUP BY username 
    ORDER BY timestamp DESC

edited Sep 12 '12 at 16:09

PhD

11,202
14
64
112

answered Feb 12 '11 at 16:03

HyperDevil

2,519
9
38
52

score 0 · Answer 4 · answered Jul 26 '13 at 13:14

0


SELECT MAX(timestamp) as timestamp, username 
    FROM bla 
    WHERE timestamp > date_sub(now(), interval 30 minute) 
    GROUP BY username 
    ORDER BY timestamp DESC

answered Jul 26 '13 at 13:14

Mohammad Towhidul Islam

264
1
2
12

MYSQL Query: latest timestamp + unique value from the last 30 minutes

4 Answers4