CODEWITHSUNDEEP
mysqlgreatest-n-per-group
Mawg
Mawg

Reputation: 40140

Limiting the number of rows returned from an inner join

I have 2 tables, descriptions of vehciles and a itorical table of lat/long as they move. Both tables have an Id key (PK and FK).

My SELECT looks like this

SELECT vehicles.id,          vehicles.description, vehicles.type, 
       positions.time_stamp, positions.latitude,   positions.longitude

       FROM         vehicles
       INNER JOIN   positions 
       ON           vehicles.id=positions.id

and I would like to ORDER and LIMIT it so that it only shows the join values for latest poisiotn reported for each vehicle.

How can I do that? Thanks

[Update] I created this SqlFiddle demo, which doesn't give the desired result.

I think I need something along the lines of

   ORDER BY     positions.time_stamp DESC
   LIMIT SELECT COUNT(*) FROM vehicles

if only that were valid syntax :-/

[Further update] for those who are concerned by the order in which thinsg execute:

  1. there will be no deletions from the positions table, only insertions
  2. insertions will be every few minutes for each vehicle
  3. I can live with the odd anomlous blip, whcih I exect would be exceeding rare
  4. it is highly unlikely the the details of the vehicles will change, all I am doing is adding new locations

Does that make it any easier?

Upvotes: 0

Views: 3885

Answers (5)

Devart
Devart

Reputation: 121922

Try this query -

SELECT
  v.id, v.description, v.type, t.id, t.time_stamp, t.latitude, t.longitude
FROM vehicles v
  JOIN (
    SELECT t1.*, COUNT(*) num FROM positions t1
      LEFT JOIN positions t2
        ON t2.id = t1.id AND t2.time_stamp >= t1.time_stamp
    GROUP BY
      t1.id, t1.time_stamp
    ) t
  ON v.id = t.id
WHERE
  num <= 2;

+----+-------------+------+----+---------------------+-----------+-----------+
| id | description | type | id | time_stamp          | latitude  | longitude |
+----+-------------+------+----+---------------------+-----------+-----------+
|  1 | Trabant     | car  |  1 | 2013-04-25 09:45:39 | 161.77994 | 102.46261 |
|  1 | Trabant     | car  |  1 | 2013-04-25 09:45:40 | 261.77994 | 402.46261 |
|  2 | Bus # 42    | bus  |  2 | 2013-04-25 09:45:39 | 221.77994 |  88.46261 |
|  2 | Bus # 42    | bus  |  2 | 2013-04-25 09:45:40 | 321.77994 |  48.46261 |
+----+-------------+------+----+---------------------+-----------+-----------+

Change num value in last line to choose desired number of output records per group. The given example outputs 2 records per group.

Upvotes: 0

Justin
Justin

Reputation: 9724

Query:

SQLFIDDLEExample

SELECT v.id
     , v.description
     , v.type
     , p.time_stamp
     , p.latitude
     , p.longitude
  FROM positions p
  JOIN vehicles v ON v.id = p.id
WHERE p.time_stamp = (SELECT MAX(l.time_stamp)
                      FROM positions l
                      WHERE l.id = p.id)

Result:

| ID | DESCRIPTION | TYPE |                   TIME_STAMP | LATITUDE | LONGITUDE |
---------------------------------------------------------------------------------
|  1 |     Trabant |  car | April, 19 2013 13:43:12+0000 | 26.77994 | 402.46261 |
|  2 |    Bus # 42 |  bus | April, 19 2013 13:43:12+0000 | 32.77994 |  48.46261 |

Upvotes: 1

spencer7593
spencer7593

Reputation: 108400

If (id, time_stamp) is unique i.e. there aren't two rows for the same vehicle and same time_stamp, you can use an inline view (mysql calls it a derived table) to get the latest time_stamp for each vehicle. You can then join that derived table to the position table, to get the other columns, something like this:

SELECT v.id
     , v.description
     , v.type
     , p.time_stamp
     , p.latitude
     , p.longitude
  FROM ( SELECT MAX(l.time_stamp) AS max_time_stamp
              , l.id
           FROM positions l
          GROUP BY l.id
       ) m
 JOIN positions p
    ON p.id = m.id
   AND p.time_stamp = m.max_time_stamp
  JOIN vehicles v
    ON v.id = p.id

You can run just that query in the inline view (assigned an alias of m in the query above, to verify that this is returning the latest time_stamp for each vehicle.

You can add an ORDER BY, if you want the results in a particular order. (This query has the potential to return more than one row for a vehicle, IFF the latest time_stamp value occurs on two (or more) rows for the same vehicle. That won't happen if no time_stamp values are duplicated for a vehicle.)

An appropriate index will speed up the creation of the derived table, and speed up the join operation back to the positions table:

 CREATE INDEX positions_IX1 ON positions (id,time_stamp)

(In other relational databases e.g. Oracle and SQL Server, we could make use of "analytic functions", but these types of functions are not yet supported in MySQL.)

Upvotes: 6

damix911
damix911

Reputation: 4443

SELECT v.id, v.description, v.type,
       cur_pos.time_stamp, cur_pos.latitude, cur_pos.longitude
FROM vehicles AS v,
(
    SELECT p.time_stamp, p.latitude, p.longitude
    FROM positions AS p
    WHERE p.id = v.id
    ORDER BY p.time_stamp DESC
    LIMIT 1
) AS cur_pos

There could be also other ways to do the same; the following is an attempt but as pointed out in the comments below, it doesn't do what the original poster asked. I'm keeping it only for reference:

SELECT v.id, v.description, v.type,
       MAX(p.time_stamp), p.latitude, p.longitude
FROM vehicles AS v INNER JOIN positions AS p ON v.id = p.id
GROUP BY v.id, v.description, v.type, p.latitude, p.longitude

Upvotes: 1

medina
medina

Reputation: 8159

SELECT v.id, v.description, v.type, p.time_stamp, p.latitude, p.longitude
FROM vehicles v
INNER JOIN positions p ON v.id = p.id
GROUP BY v.id
ORDER BY p.time_stamp DESC

Upvotes: 1

Related Questions