MySQL Query get the last N rows per Group

Question

Suppose that I have a database which contains the following columns:

VehicleID|timestamp|lat|lon|

I may have multiple times the same VehicleId but with a different timestamp. Thus VehicleId,Timestamp is the primary key.

Now I would like to have as a result the last N measurements per VehicleId or the first N measurements per vehicleId.

How I am able to list the last N tuples according to an ordering column (e.g. in our case timestamp) per VehicleId?

Example:

|VehicleId|Timestamp|
         1|1
         1|2
         1|3
         2|1
         2|2
         2|3
         5|5
         5|6
         5|7

possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) — Giorgos Betsos, Sep 22 '15 at 11:54

score 4 · Accepted Answer · answered Sep 22 '15 at 11:54

4

In MySQL, this is most easily done using variables:

select t.*
from (select t.*,
             (@rn := if(@v = vehicle, @rn + 1,
                        if(@v := vehicle, 1, 1)
                       )
             ) as rn
      from table t cross join
           (select @v := -1, @rn := 0) params
      order by VehicleId, timestamp desc
     ) t
where rn <= 3;

answered Sep 22 '15 at 11:54

Gordon Linoff

1,242,037
58
646
786

So a SQL alone way to do that is not available? – nikosdi Sep 22 '15 at 12:20
@nikosdi . . . There are other ways, but this is a single SQL statement and the best approach in MySQL. – Gordon Linoff Sep 24 '15 at 21:45

MySQL Query get the last N rows per Group

1 Answers1

Linked