Geek Answers Handbook

MYSQL query: last timestamp + unique value in the last 30 minutes

I need to get the last rows with a unique value from my mysql table. A simple table layout is a timestamp (now ()) and a username column. The table gets new data a couple of times per second, and I need the last row, where the username is unique.

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

This query does not seem to return the last values, perhaps because the group is doing something I don't want ...

+5
source share
4 answers
SELECT MAX(timestamp) as timestamp, username 
    FROM bla 
    WHERE timestamp > (now() - interval 30 minute) 
    GROUP BY username 
    ORDER BY timestamp DESC
+2
source

If you are trying to watch the last 30 minutes, I think you want to use more than, rather than less.

... WHERE timestamp > (now() - interval 30 minute) ...
+6
source

, , , , LEFT JOIN , , LEFT JOIN , , :

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

bla w/ , (next_bla.created_at IS NULL), .. .

LIMIT .

, , GROUP BY : http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html

Andomar - MySQL "Group By" " "

+3
source 

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

More articles:


All Articles