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 w开发者_Python百科indow)

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.

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.

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