CODEWITHSUNDEEP
mysql
redfrogsbinary
redfrogsbinary

Reputation: 629

getting the latest records from mysql

I have a mysql database with several records. each record has an name. It is not unique , meaning that the same name appears many times in the table as the values are updated. How can i create a query that will return only the latest names that were updated over the last 5 min. (every name is updated many times per minute. I want only the latest entry within the 5 min window)

Upvotes: 3

Views: 796

Answers (3)

bhamby
bhamby

Reputation: 15469

If you add a TIMESTAMP column, you should be able to get the newest for a name that was added in the past 5 minutes by doing a query like so:

SELECT *
FROM yourtable AS a
WHERE a.ts = 
    (SELECT MAX(ts)
     FROM yourtable AS b
     WHERE b.ts > NOW() - INTERVAL 5 MINUTE
       AND b.name = a.name)

Attempt #2:

SELECT *
FROM yourtable AS a
INNER JOIN (SELECT name, MAX(ts) AS ts
     FROM yourtable
     GROUP BY name
     HAVING MAX(ts) > NOW() - INTERVAL 5 MINUTE) AS b
 ON a.name = b.name
AND a.ts = b.ts

Upvotes: 0

Anthony Accioly
Anthony Accioly

Reputation: 22481

Have a TIMESTAMP column for your table. Select only the records where the time-stamp value is between five minutes ago and now (See Date and Time Functions for further info). Optionally group by name and select the row with maximum time-stamp value if you only want the latest update for every name.

Upvotes: 1

Konerak
Konerak

Reputation: 39773

You should add a column with a TIMESTAMP to your table.

This column will automatically be initialised and updated to the current time when the row gets changed, so taking all the columns that changed the last X minutes then becomes a simple query.

SELECT *
FROM yourtable
WHERE yourtimestamp  > NOW() - INTERVAL 5 MINUTE

Without such a column, you can't really know when a row has been changed, unless you find another way to track changes. A trigger or the binary log could help, but both solutions are not easier and certainly not faster than adding a column.

Upvotes: 4

Related Questions