SQL query to select recent X records for a specific non-primary field

Question

I'm having difficulty setting up a slightly more complex SQL query. What I'm trying to do is select the last 24 records for each zr_miner_id, but I keep getting SQL timeouts (there are about 40,000 records in the table). So, let's say 200 entries for zr_miner_id1 and 200 for zr_miner_id2, I get 48 results.

So far I have come to the following request. This must do to select each result in zec_resultswhich has less than 24 new entries with the same zr_miner_id. I could not think of a better way to accomplish this task, but then again, I have not yet advanced in SQL.

SELECT results_a.*
FROM   zec_results results_a 
WHERE  (
    SELECT COUNT(results_b.zr_id) 
    FROM   zec_results AS results_b 
    WHERE  results_b.zr_miner_id = results_a.zr_miner_id 
    AND    results_b.zr_id >= results_a.zr_id
) <= 24 

Answer 1

!

SELECT r.*
FROM (SELECT r.*,
             (@rn := if(@m = r.zr_miner_id, @rn + 1,
                        if(@m := r.zr_miner_id, 1, 1)
                       )
             ) as rn
      FROM zec_results r CROSS JOIN
           (SELECT @m := -1, @rn := 0) params
      ORDER BY r.zr_miner_id, r.zr_id DESC
     ) r
WHERE rn <= 24 ;

, . (zr_miner_id, zr_id).