CODEWITHSUNDEEP
mysqlgreatest-n-per-group
gorksybinary
gorksybinary

Reputation:

Getting the newest record from a database Multiple queries

I have a mysql database with vehicles records. I need a fast query that will return the newest records of those records that were updated within the last 4 minutes. For example vehicle "A" may be updated several times a minute so it will appear many times within the last 4min. Same with vehicle B C etc. I need only the most recent entries for each vehicle within a 4 min window. I have tried like this

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)

but it takes too long to produce results >10seconds.

Upvotes: 2

Views: 73

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

To get all the rows for the latest updates and not only the name and timestamp:

SELECT t.*
FROM
      TableX AS t
  JOIN
      ( SELECT name
             , MAX(ts) AS maxts
        FROM TableX
        WHERE ts >  NOW() - INTERVAL 4 MINUTE
        GROUP BY name
      ) AS grp
    ON  grp.name = t.name
    AND grp.maxts = t.ts

You'll need at least an index on the timestamp column for this query.

Upvotes: 1

Noam Kremen
Noam Kremen

Reputation: 418

You don't need the self-join.

select max(ts), name from Table1
where ts >  NOW() - INTERVAL 5 MINUTE
group by name

Upvotes: 3

Related Questions