Limiting the number of rows returned from an inner join

Question

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:

there will be no deletions from the positions table, only insertions
insertions will be every few minutes for each vehicle
I can live with the odd anomlous blip, whcih I exect would be exceeding rare
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?

I would suggest looking at the answers here as well: http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group. The answer you accepted thought technically correct, in practice might turn out very inefficient, since MySQL does not like correlated subqueries. — newtover, Apr 23 '13 at 12:51

spencer7593 · Answer 1 · 2013-04-19T05:13:36.207

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.)

This solution avoids any correlated subqueries so should perform far more efficiently than the alternatives above. — Kickstart, Apr 23 '13 at 10:21
+1 this solution tends to perform much better than the accepted answer. — Bill Karwin, Oct 07 '13 at 03:25

score 1 · Answer 2 · answered Apr 19 '13 at 03:34

1

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

answered Apr 19 '13 at 03:34

medina

8,051
4
25
24

1

Since the ORDER BY operation happens after the GROUP BY operation, there is no guarantee that the values returned from the positions table will be from the row with the latest time_stamp. – spencer7593 Apr 19 '13 at 04:01
spencer, thanks for the information. I hadn't realised it. cheers! – medina Apr 23 '13 at 23:52

damix911 · Answer 3 · 2013-04-19T04:25:11.200

1

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

edited Apr 19 '13 at 04:25

answered Apr 19 '13 at 03:40

damix911

4,165
1
29
44

2

The second query will return a result set, but, there is NO GUARANTEE that the values returned for p.latitude and p.longitude will be from the row with the latest time_stamp. – spencer7593 Apr 19 '13 at 03:59
1

The second one seemed a little too easy to me, and indeed now I note that it has problems, thank you for pointing that out. However, I think that what would actually happen is that *every* vehicle will be joined with *every* position, and if a vehicle was in the same location *more than one time*, only the last time stamp would be kept. But yes, it's the wrong output. What about the first one? – damix911 Apr 19 '13 at 04:22

score 1 · Accepted Answer · answered Apr 19 '13 at 13:53

1

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 |

answered Apr 19 '13 at 13:53

Justin

9,634
6
35
47

I will award the bounty in 23 hours, when the system permits it. Thanks again!! – Mawg says reinstate Monica Apr 23 '13 at 04:24
2

With a large number of rows in the positions table, a query that performs a join to an inline view will be MUCH MORE EFFICIENT than this one. That's because for this query, MySQL will execute that dependent subquery for each and every row in the positions table. (It's unlikely that you won't really notice a performance difference with a small number of rows, but scale that positions table up to a couple million rows, and you will notice the difference.) – spencer7593 Apr 23 '13 at 20:41
Spencer thanks for comment, I will try your sugesstion, to see what is better. – Justin Apr 23 '13 at 22:02
A join to an inline view is not always more efficient than a dependent subquery. On the one hand, you have the cost of materializing the inline view (as a temporary myisam table) and a join operation, vs. repeated executions of a dependent subquery. The dependent subquery approach is fast, when the number of executions is small, but it "bites" performance-wise when the dependent subquery gets executed millions and millions of times. – spencer7593 Apr 24 '13 at 18:50

score 0 · Answer 5 · answered Apr 25 '13 at 07:01

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.

Limiting the number of rows returned from an inner join

5 Answers5