Mikail Mikail - 1 month ago 7
MySQL Question

want to return latest records where results are grouped by

I'm grouping results by SERIAL_NUMBER and I'd like to display the last records for each group according to record ID DESC this what I've got so far:

SELECT * FROM

(SELECT `SERIAL_NUMBER`, `PART_NUMBER` , `POSITION` , `DUE_CAP_CHECK_DATE` , `DUE_OVERHAUL_DATE`
FROM `history_card` ORDER BY `HISTORY_ID` DESC ) AS X

GROUP BY `SERIAL_NUMBER`


But It does not return the last record, it returns the first one ;(

Answer

You are misusing the heinously confusing nonstandard MySQL extension to GROUP BY. Read this. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html (This extension is like a talking horse. We don't wonder that it works badly. We wonder that it works at all.)

You can get the latest (largest) value of HISTORY_ID for each value of SERIAL_NUMBER from your table like this:

  SELECT MAX(HISTORY_ID) FROM history_card  GROUP BY SERIAL_NUMBER

Then you can use that set of HISTORY_ID values to retrieve what you want from your table.

  SELECT SERIAL_NUMBER, PART_NUMBER, POSITION, DUE_CAP_CHECK_DATE, DUE_OVERHAUL_DATE
    FROM history_card
   WHERE HISTORY_ID 
         IN (SELECT MAX(HISTORY_ID) FROM history_card  GROUP BY SERIAL_NUMBER)            
Comments