CODEWITHSUNDEEP
mysqlsql-order-by
iss42
iss42

Reputation: 2816

MySQL - query where Order By doesn't work as expected

I'm expecting the resulting "user_id" to be the one corresponding to the 1st records in the Location table in DESCending order. But I am getting the "user_id" of the 1st record in ASCending order / or ID order.

SELECT Device.name, Location.user_id
  FROM    locations Location
       LEFT JOIN devices Device
            ON Device.id = Location.device_id
 WHERE Device.id IN (491)
GROUP BY Location.device_id
ORDER BY Location.location_datetime DESC

Anyone know why? How to improve it?

Thanks!

Upvotes: 1

Views: 1270

Answers (2)

fthiella
fthiella

Reputation: 49089

You could use something like this:

SELECT
  devices.name,
  (select locations.user_id
   from locations
   where locations.device_id = devices.id
   order by locations.location_datetime desc
   limit 1)
FROM
  devices
WHERE
  device.id = 491

you can't use group by with nonaggregated columns to get the first or the last record: it is not guaranteed that it will always work.

EDIT: if you need to select more than one field from locations, you could use this:

SELECT l1.*, devices.name
FROM
  locations l1 inner join
  (select device_id, max(location_datetime) as maxdt
   from locations
   group by device_id) l2
  on l1.device_id = l2.device_id and l1.location_datetime=l2.maxdt
  left join devices
  on devices.id = l1.device_id

this query will select last location for each device_id. If there are more than one location with the same maximum datetime, this query will return each one. If you need just one, and don't mind which one is selected, you could also add a GROUP BY l1.device_id clause at the end.

Upvotes: 2

echo_Me
echo_Me

Reputation: 37243

try this

      SELECT Device.name, Location.user_id ,Location.location_datetime
      FROM    locations Location
      LEFT JOIN devices Device
        ON Device.id = Location.device_id
     WHERE Device.id IN (491)

    ORDER BY Location.user_id DESC
    GROUP BY Location.device_id

Upvotes: 0

Related Questions