MySQL statement, 12 unique records with the most latest epoch

Question

I have a table which contains the following columns 'rack_id', 'epoch' and 'temp'.

I want a statement which picks the twelve latest records (based on the epoch) for a selection of rack_id's and sorts them by rack ID.

What I have so far is:

SELECT * FROM `temp_top_of_rack` WHERE `rack_id` BETWEEN 145 AND 156 ORDER BY `rack_id` ASC;

The only bit that I can't figure out to do (even after looking around the internet and previous questions) is to extend the statement to say that each record should be the most recent (based on the epoch).

To clarify in the example statement above I want the result to be a single row for each rack_id and for that record to be the most recent based on the epoch column.

possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Clockwork-Muse, Jul 06 '14 at 08:51

score 0 · Answer 1 · answered Jul 05 '14 at 18:20

select t1.* 
from temp_top_of_rack t1
(
   SELECT rack_id, 
          max(epoch_column) as max_epoch
   FROM temp_top_of_rack 
   WHERE rack_id BETWEEN 145 AND 156
   GROUP BY rack_id
) t2 on t1.rack_id = t2.rack_id and t1.epoch_column = t2.max_epoch
ORDER BY t1.rack_id ASC

VMai · Accepted Answer · 2014-07-05T22:32:54.430

A solution with an correlated subselect would be:

SELECT
    *
FROM
    temp_top_of_rack t1
WHERE
    t1.rack_id BETWEEN 145 AND 156
AND
    t1.epoch = (
        SELECT
            MAX(t2.epoch)
        FROM
            temp_top_of_rack t2
        WHERE
            t1.rack_id = t2.rack_id
    )
ORDER BY
    rack_id
LIMIT 12

Explanation:

This returns for every rack_id the most recent row. I assume that there are rows for every rack_id, because there are only 12 different rack_id values. So the LIMIT clause is superfluous and could be removed

MySQL statement, 12 unique records with the most latest epoch

2 Answers2