Luke Smith Luke Smith - 1 month ago 5
MySQL Question

MYSQL select highest revision from table for each row

I have a table setup like this

ID JOBID REPORTNAME REVISION PDFLOCATION

1 1 RPT1 1 /var/rpt1.pdf
1 2 RPT2 1 /var/rpt2.pdf
1 1 RPT1 2 /var/rpt3.pdf


How do i select all the rows but i want only the highest revision per jobid
and example result from the quest should be

1 2 RPT2 1 /var/rpt2.pdf
1 1 RPT1 2 /var/rpt3.pdf


Im sure that maybe i need to do groups but i am totally lost with this concept, any help would be appreciated!

Answer

If I understand correctly, you want to select the record having the maximum revision number for each ID/JOBID group.

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT ID, JOBID, MAX(REVISION) AS REVISION
    FROM yourTable
    GROUP BY ID, JOBID
) t2
    ON t1.ID       = t2.ID AND
       t1.JOBID    = t2.JOBID AND
       t1.REVISION = t2.REVISION
Comments