CODEWITHSUNDEEP
mysqlsqlsortinggreatest-n-per-group
tom_tom
tom_tom

Reputation: 465

Get latest entry of group of data

I have a mySQL database to store position information of bikes. For the 'last position' method I want to get only the last entry of a bike but from all bikes.

Example:

ID | ## LAT ## | ## LNG ## | #### TIMESTAMP #### | BIKE_ID
----------------------------------------------------------
16 | N47.68526 | E16.59032 | 2015-01-12 14:49:51 | 3
17 | N47.36345 | E16.12096 | 2015-01-12 14:50:27 | 9
18 | N44.12526 | E15.12562 | 2015-01-12 14:51:45 | 1
19 | N47.87654 | E16.54323 | 2015-01-12 14:51:51 | 3
20 | N47.12345 | E16.12341 | 2015-01-12 14:52:27 | 1
21 | N47.12531 | E15.12561 | 2015-01-12 14:52:45 | 9
22 | N44.12531 | E16.12561 | 2015-01-12 14:53:01 | 1
23 | N47.12531 | E15.12561 | 2015-01-12 14:53:18 | 9
...

I want an output like:

ID | ## LAT ## | ## LNG ## | #### TIMESTAMP #### | BIKE_ID
----------------------------------------------------------
19 | N47.87654 | E16.54323 | 2015-01-12 14:51:51 | 3
22 | N44.12531 | E16.12561 | 2015-01-12 14:53:01 | 1
23 | N47.12531 | E15.12561 | 2015-01-12 14:53:18 | 9

Upvotes: 0

Views: 160

Answers (3)

O. Jones
O. Jones

Reputation: 108641

This problem is fairly common, and has an elegant optimization. If your last entry is guaranteed to be the one with the highest value of id (that is, if your id column is an autoincrement column and the table is logging new data), do this:

 SELECT t.BIKE_ID, t.LAT, t.LNG, t.`TIMESTAMP`
   FROM table t
   JOIN (SELECT MAX(ID) AS ID 
          FROM table
         GROUP BY BIKE_ID
        ) m ON t.ID = m.ID

The inner query (MAX ... GROUP BY) efficiently identifies the most recent entry for each bike. If you put the compound index (BIKE_ID, ID) on your table, this query will be very efficient indeed, because it can do a so-called loose index scan.

Pro tip. Make your latitudes and longitudes into FLOAT values, where North and East are positive numbers.

Upvotes: 0

xQbert
xQbert

Reputation: 35323

1st get a set of data with the the max timestamp for each bike, then join it back to the whole set based on bike id and the max timestamp. This is assuming you need additional data from the base table. If you don't the inner select alone would work...

If you need additional data from table...

SELECT t.id AS 'ID', 
       t.lat AS '## LAT ##', 
       t.lng AS '## LNG ##', 
       t.timestamp AS '#### TIMESTAMP ####', 
       t.bike_Id AS 'BIKE_ID' 
FROM table_Name t
INNER JOIN (SELECT bike_ID, max(timestamp) mts 
            FROM tableName
            GROUP BY bike_ID) t2 
  on t2.bike_ID = t.bike_ID 
 and t2.mts = t.timestamp

If not... Just use an aggregate...

SELECT bike_ID, 
       max(timestamp) mts 
FROM tableName
GROUP BY bike_ID

Upvotes: 2

Marc B
Marc B

Reputation: 360572

Basically this:

SELECT * 
FROM yourtable
INNER JOIN (
   SELECT BIKE_ID, MAX(TIMESTAMP)
   FROM yourtable
   GROUP BY BIKE_ID
) AS foo ON ((yourtable.BIKE_ID = foo.BIKE_ID) AND (yourtable.TIMESTAMP = foo.TIMESTAMP))

The subquery gets the highest timestamp for every bike ID, which you then use to self-join against the original table to select the other fields which correspond to those id/timestamps.

Upvotes: 0

Related Questions