Get corresponding column to MAX value in MySQL

Question

I don't understand what I need to do to get a value from a different column of a joined table when using MAX().

SELECT layout.NAME,layout.ID,MAX(inventory_reports.CLOSETIME)
              FROM layout
              LEFT JOIN inventory_reports ON layout.ID = inventory_reports.POSID
              WHERE layout.INVENTORY = 1 AND layout.AVAILABLE = 1
              GROUP BY layout.ID
              ORDER BY layout.NAME

The table inventory_reports also contains a column called CLOSER. How can I get that value for the row matching the MAX(inventory_reports.CLOSETIME) ?

I tried playing around with joining on subqueries, but all my attempts so far have given me incorrect results.

score 3 · Accepted Answer · answered Jun 14 '16 at 14:27

3

Try this:

SELECT l.NAME,
       l.ID,
       ir1.MAX_CLOSETIME,
       ir2.CLOSER
FROM layout AS l
LEFT JOIN (
   SELECT POSID, MAX(inventory_reports.CLOSETIME) AS MAX_CLOSETIME
   FROM inventory_reports
   GROUP BY POSID
) AS ir1 ON l.ID = ir1.POSID
LEFT JOIN inventory_reports AS ir2 
   ON ir1.POSID = ir2.POSID AND ir1.MAX_CLOSETIME = ir2.CLOSETIME
WHERE l.INVENTORY = 1 AND l.AVAILABLE = 1
ORDER BY l.NAME, l.ID

answered Jun 14 '16 at 14:27

Giorgos Betsos

71,379
9
63
98

Yes. This worked. I'll have a closer look and see if I can understand what's going on here. – nickdnk Jun 14 '16 at 14:29
This question may have been a duplicate, but IMO - this was a more clearer solution to the OP's problem. – Erutan409 Apr 24 '18 at 13:26
This will often work, but I think it relies on the assumption that there will not be two identical CLOSETIME for the same POSID. – Mike Aug 26 '21 at 13:52

Get corresponding column to MAX value in MySQL

1 Answers1