Selecting the last record of a MySQL query result-CodePudding

I build a database that stores devices and their deployments. Devices can be in the storage, or deployed somewhere, then returned to the storage and deployed somewhere else.

The current database currently looks like this:

Devices

 ---- --------- 
|id  |serialno |
 ---- --------- 
|1   |serial1  |
 ---- --------- 
|2   |serial2  |
 ---- ---------

Deployments

 ---- --------- --------- 
|id  |deviceID |location |
 ---- --------- --------- 
|1   |1        |location1|
 ---- --------- --------- 
|2   |1        |location2|
 ---- --------- --------- 
|3   |2        |location3|
 ---- --------- --------- 
|4   |2        |location4|
 ---- --------- ---------

Now, I would need a query that lists devices with only their last deployment, but no matter what I try, it always gives me all the records. Even if I use distinct.

SELECT distinct devices.id as devID, serialno, location
FROM devices INNER JOIN deployments ON devices.id = deployments.deviceID
ORDER BY deployments.id DESC

I would expect a result like:

 ----- --------- --------- 
|devID|serialno |location |
 ----- --------- --------- 
|2    |serial2  |location4|
 ----- --------- --------- 
|1    |serial1  |location2|
 ----- --------- ---------

But it returns all rows:

 ----- --------- --------- 
|devID|serialno |location |
 ----- --------- --------- 
|2    |serial2  |location4|
 ----- --------- --------- 
|2    |serial2  |location3|
 ----- --------- --------- 
|1    |serial1  |location2|
 ----- --------- --------- 
|1    |serial1  |location1|
 ----- --------- ---------

If I use group by, it does return one row of each device, but the FIRST deployment of them, not the last one.

How could I create a query that results the last deployments of the devices?

CodePudding user response：

First you need to get the max id from deployments for each device then that max id again need to pass to your inner join query.

Following is the query:

SELECT d.id AS devID, d.serialno, dp.location 
FROM devices d 
INNER JOIN deployments dp on (d.id = dp.deviceID)
WHERE dp.id = (SELECT MAX(ic.id) 
              FROM deployments ic where ic.deviceID = dp.deviceID)
ORDER BY dp.id DESC;

Thanks