MySQL selecting distinct values order by datetime

Question

(I Can´t Speak English very well, but i'll try my best)

I have two tables.

table1

id   carid    user
---------------------
1  |  A001 |   user1
2  |  A002 |   user1
3  |  A003 |   user2
4  |  A002 |   user3

table2

id  carid       datetime            lat       lon
----------------------------------------------------
1 | A001 | 2013-25-06 10:00:00 | -23.0000 | -46.0000
2 | A002 | 2013-25-06 10:01:00 | -24.3500 | -45.3200
3 | A002 | 2013-25-06 10:02:00 | -24.3800 | -45.3300
4 | A001 | 2013-25-06 10:05:00 | -23.0500 | -46.1000
5 | A003 | 2013-25-06 10:07:00 | -24.3500 | -45.3200
6 | A001 | 2013-25-06 10:10:00 | -23.0700 | -46.1200

I need to select each distinct registry "carid" from "user1" ordered by datetime

Result I need:

   carid         datetime         lat       lon
--------------------------------------------------
   A001 |  2013-25-06 10:10:00 | -23.0700  |-46.1200
   A002 |  2013-25-06 10:02:00 | -24.3800  |-45.3300

The way I’m actually making is selecting all “carid” from the user I want, and selecting each row individually through .net.

`SELECT carid FROM table1 where user = “user1”;`

carid
-----
A001
A002

Then selecting the row I want:

SELECT * FROM table2 WHERE car_id='A001' ORDER BY datetime DESC limit 1
SELECT * FROM table2 WHERE car_id='A002' ORDER BY datetime DESC limit 1

But depending on the number of registries "carid’s" from that user i have to do a lot of querys. I don’t know if it’s possible to do this with a single SELECT improving the way I’m doing, but that’s what I have tried:

SELECT car_id, datetime, lat, lon from table1 
INNER JOIN table2 on carid = car_id
WHERE user = 'user1'
GROUP BY carid
ORDER BY datetime DESC;

Result:

 carid    datetime            lat          lon
------------------------------------------------------
 A002 | 2013-25-06 10:01:00 | -24.3500 | -45.3200
 A001 | 2013-25-06 10:02:00 | -23.0000 | -46.0000

And I also tried this:

SELECT car_id, MAX(datetime) as datetime, lat, lon from table1 
INNER JOIN table2 on carid = car_id
WHERE user = 'user1'
GROUP BY carid
ORDER BY datetime DESC;

Result:

 carid     datetime            lat           lon
------------------------------------------------------
 A001 |  2013-25-06 10:10:00 | -23.0000 | -46.0000
 A002 |  2013-25-06 10:02:00 | -24.3500 | -45.3200

But the result i got is wrong. I don’t know what to do without select all the rows, , which is a slower way than the way I'm really making it.

Any thoughts?

Answer 1

You can join on table2 twice, once to get the max(datetime) for each carId and the second to get the lat and lon associated with the carId and datetime:

select t1.carid, t2.datetime, t2.lat, t2.lon
from table1 t1
inner join
(
  -- get the lat/lon associated with each carid and max datetime
  select t2.carid, t2.datetime, t2.lat, t2.lon
  from table2 t2
  inner join
  (
    -- get the max datetime for each carid
    select carid, max(datetime) datetime
    from table2
    group by carid
  ) d
    on t2.carid = d.carid
    and t2.datetime = d.datetime
) t2
  on t1.carid = t2.carid
where user = 'user1';

See SQL Fiddle with Demo.

Your query with the max() was returning wrong lat and lon values because you are only grouping by the carid so MySQL can arbitrarily select the values for the columns in the select list that are not in an aggregate function or a GROUP BY. This behavior is due to MySQL's Extensions to GROUP BY.

From the MySQL Docs:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. ... You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.

In order to be sure that you return the correct values, you will want to use a subquery similar to the above.