MySQL selects the order of values ​​by date

Question

(I do not speak English very well, but I will try)

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 individual registry "carid" from "user1" sorted 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 actually do this is to select all the "carid" from the user I want, and select each line separately through .net.

`SELECT carid FROM table1 where user = "user1";` 

  carid
 -----
 A001
 A002

Then select the line 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 "carids" registries from this user, I have to make a lot of queries. I don't know if it is possible to do this with a single SELECT that improves Im's performance, but here is what I 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 that I got is wrong. I do not know what to do without highlighting all the lines, which is slower than the way I really do it.

Any thoughts?

Answer 1

You can join table2 twice, once to get max(datetime) for each carId , and the second for lat and lon associated with 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 max() returned the wrong lat and lon values ​​because you are only grouping carId , so MySQL can arbitrarily select values ​​for columns in the select list that are not in the aggregate function or GROUP BY. This behavior is due to the MySQL Extensions for GROUP BY .

In MySQL Docs:

MySQL expands the use of GROUP BY so that the selection list can refer to non-aggregated columns not named in the GROUP BY clause .... You can use this function to improve performance by avoiding unnecessary sorting and grouping of columns. However, this is useful primarily when all the values ​​in each non-aggregated column not named in GROUP BY are the same for each group. The server can select any value from each group, therefore, if they do not match, the selected values ​​are undefined. Moreover, the selection of values ​​from each group cannot depend on the addition of an ORDER BY clause. The result set is sorted after the values ​​have been selected, and ORDER BY does not affect the values ​​that the server selects.

To make sure you return the correct values, you will want to use a subquery similar to the one above.

Answer 2

I think the following query should work for your scenario. I have not tried the same thing that I have no data.

 SELECT t1.carid, t2.datetime, t2.lat, t2.lon FROM table1 t1 JOIN table2 t2 ON t1.carid = t2.carid AND t1.user='user1' GROUP BY carid HAVING t2.datetime=MAX(datetime); 

Let me know if this works.