Latest Group By MYSQL + link Table

Question

I'm experiencing trouble with the a linking table and retrieving the lastUsed values.

Linking table:

link_devices_user

id | deviceId |  shopId  |
 1 |   359    |    46    |
 2 |   1339   |    46    |
 3 |   1328   |    45    |
 4 |   882    |    46    |

system_devices

id    | carId   | registerId   | lastUsed            |
 359  |   350   |  regi1       | 2014-01-03 09:00:00 |
 1339 |   350   |  regi2       | 2013-01-03 09:00:00 |
 1328 |   160   |  regi3       | 2012-01-03 09:00:00 |
 882  |   150   |  regi4       | 2014-01-03 08:59:00 |

Now I need to retrieve the latest unique carId from system_devices that is connected to shopId 46. So in this case, the results should be.

882  |   150   |  regi4       | 2014-01-03 08:59:00 |
359  |   350   |  regi1       | 2014-01-03 09:00:00 |

I now have the following query. This gives me the unique carId but not the latest unique carId. What should i change?

SELECT system_devices.id, 
       carId,        
FROM   link_devices_user 
       INNER JOIN system_devices 
               ON link_devices_user.deviceId = system_devices.id 
WHERE  link_devices_user.shopid = '46' 
GROUP  BY system_devices.carId 
ORDER  BY system_devices.lastUsed DESC 

Answer 1

Try this:

SELECT s.*
FROM system_devices s LEFT JOIN system_devices s1
   ON (s.carId = s1.carId AND s.lastUsed < s1.lastUsed)
   INNER JOIN link_devices_user ld ON s.id = ld.deviceId
WHERE (s1.id IS NULL) AND (ld.shopId = 46)
ORDER BY carId

http://sqlfiddle.com/#!2/158d9/4

You can check the SQL fiddle sample provided that gives the results required.

Answer 2

Try this:

SELECT A.id, A.carId, A.registerId, A.lastUsed 
FROM (SELECT sd.id, sd.carId, sd.registerId, sd.lastUsed 
      FROM system_devices sd
      INNER JOIN link_devices_user ld ON sd.id = ld.deviceId
      WHERE ld.shopId = 46
      ORDER BY sd.id, sd.carId DESC
     ) AS A 
GROUP BY A.id

OR

SELECT sd.id, sd.carId, sd.registerId, sd.lastUsed 
FROM system_devices sd
INNER JOIN link_devices_user ld ON sd.id = ld.deviceId
INNER JOIN (SELECT id, MAX(carId) carId FROM system_devices GROUP BY id) A ON A.id = sd.id AND A.carId = sd.carId 
WHERE ld.shopId = 46
ORDER BY sd.id