how to select a row relative to the max of a row

Question

I have this query -

SELECT d.sn,d.alias,d.CompanyID,max(checkdate),checkin 
FROM tbl_checkin ch 
right join tbl_device d 
on ch.sn=d.sn 
group by ch.sn 
order by max(checkdate) desc

these selects random checkin when runned

but what i want to acheive is a query where by its going to select the checkin based on the max(checkdate) that is display the checkin that corresponds to the max(checkdate)

sn              alias            compid      max(checkdate)    checkin
4223642350043   DANTATA-SAWOE_1  1057     2014-06-05            14:10:01
4223642350051   DANTATA-SAWOE_2  1057     2014-05-28           17:04:46
4223642350067   DANTATA-SAWOE_3A 1057     2014-05-13           15:50:44
0278135000136   DANTATA-SAWOE_4  1057       null                    null

i'll be very grateful if i can get an answer

score 0 · Answer 1 · answered Jun 24 '14 at 18:07

0

If I understand you correctly you only want to return 1 row - use LIMIT for that:

SELECT d.sn,d.alias,d.CompanyID,max(checkdate),checkin 
FROM tbl_checkin ch 
RIGHT JOIN tbl_device d 
ON ch.sn=d.sn 
GROUP BY ch.sn 
ORDER BY MAX(checkdate) DESC
LIMIT 1

answered Jun 24 '14 at 18:07

Jay Blanchard

34,243
16
77
119

Nope, not the limit. I think he's saying that because of the max(checkdate) column the corresponding checkin value isn't from the same row – JohnP Jun 24 '14 at 18:10
If you understand this correctly, then I'd be tempted to suggest that your powers are verging on psychic – Strawberry Jul 01 '14 at 16:59
@JayBlanchard, because the ch row returned is any of the matching rows, while the MAX() is only the max of all the rows. Without an aggregator to force the checkin to a specific value, it's essentially random. You can see my example of doing an extra LEFT JOIN to enforce that the first LEFT JOIN only returns the row with the max (or equal to max) row. – Kevin Nelson Jul 01 '14 at 16:59

score 0 · Answer 2 · edited May 23 '17 at 12:05

0

To get the checkin value that corresponds (on the same row) with your MAX(checkdate), you have to create a sub-query or additional left-join to filter the value for tbl_checkin.checkin. This has been answered elsewhere, as this is a common dilemma:

SQL Select only rows with Max Value on a Column

In any case, this should work with perhaps minor revisions if I typo'd:

SELECT d.sn,d.alias,d.CompanyID,ch.checkdate,ch.checkin 
FROM tbl_device d
    LEFT JOIN tbl_checkin ch on ch.sn=d.sn 
    LEFT JOIN tbl_checkin ch2 on ch2.sn=d.sn AND ch2.checkdate > ch.checkdate
WHERE ch2.checkdate IS NULL
ORDER BY ch.checkdate DESC

Note: if you have two ch rows that have the same MAX(checkdate), then the ch.checkin can still be either of the two rows.

edited May 23 '17 at 12:05

Community

1
1

answered Jun 24 '14 at 18:15

Kevin Nelson

7,613
4
31
42

@Strawberry, you are right in probably every other version of SQL, but MySQL actually encourages their extension of GROUP BY for optimization: http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html. So, I'm not conflating, just taking advantage of MySQL's peculiarities. If the `MAX(checkdate)` is not guaranteed to be unique, but you need only one row, then it's still allowed to use the GROUP BY to ensure only one row of checkin/checkdate per device. Otherwise, you have to LEFT JOIN a SUB-QUERY with a `ORDER BY checkdate DESC LIMIT 1` which MySQL is not able to optimize as well[cont... – Kevin Nelson Jul 01 '14 at 19:10
[...cont] However, in this case, the questioner didn't give the impression that MAX(checkdate) could match more than one row, so in the absence of the need for it, I removed it from the query as unnecessary. – Kevin Nelson Jul 01 '14 at 19:12

how to select a row relative to the max of a row

2 Answers2