How to QUERY top n records of each group within a table?

Question

I have a log table which has host_name, drive_id and process_run_id as primary key.

I need the MySQL statement which will get the top n process_run_ids for each unique host_name and drive_id.

CURRENT TABLE:

|host_name|drive_id |process_run_id|free_space|
|       A |   C     |          1   |       500|
|       A |   C     |          2   |       500|
|       A |   C     |          3   |       570|
|       A |   C     |          4   |      1000|
|       B |   C     |          1   |       769|
|       B |   C     |          2   |      4167|
|       B |   C     |          3   |      3244|
|       B |   D     |          1   |      7654|
|       B |   D     |          2   |        76|
|       B |   D     |          3   |       435|
|       B |   D     |          4   |       243|
|       C |   C     |          1   |     23443|
|       C |   C     |          2   |      4324|
|       C |   C     |          3   |      1232|
|       C |   C     |          4   |      9777|

DESIRED QUERY RESULT (Top 2):

|host_name|drive_id |process_run_id|free_space|
|       A |   C     |          3   |       570|
|       A |   C     |          4   |      1000|
|       B |   C     |          2   |      4167|
|       B |   C     |          3   |      3244|
|       B |   D     |          3   |       435|
|       B |   D     |          4   |       243|
|       C |   C     |          3   |      1232|
|       C |   C     |          4   |      9777|

ATTEMPT :

SELECT space_free, host_name, drive_id, process_run_id
FROM
(SELECT space_free, host_name, drive_id, process_run_id,
              @host_rank := IF(@current_host = CONCAT(host_name, drive_id), @host_rank + 1, 1) AS host_rank,
              @current_host := CONCAT(host_name, drive_id)
   FROM sandbox_yohal.main_fds_history_list
   ORDER BY host_name, process_run_id DESC
 ) ranked
WHERE host_rank <= 2;

What have you tried? SO is not your personal code writing service — Arion, Jun 27 '19 at 13:25
We'll i've been trying different top N queries since yesterday and to be honest I'm not that familiar with MySQL, this is one step of a project i'm doing to collect disk space info for a call center and create a monitoring tool for the info. @Arion — Youni Halloumi, Jun 27 '19 at 13:35
please, replace image with [Plain Text](https://www.tablesgenerator.com/text_tables) and add some tests you've done. — Alberto Moro, Jun 27 '19 at 13:46
Possible duplicate of [Rank function in MySQL](https://stackoverflow.com/questions/3333665/rank-function-in-mysql) — Raymond Nijland, Jun 27 '19 at 14:04
The trick is using RANK()/ROW_NUMBER() or simulate that with MySQL user variables and filter on that rank number.. — Raymond Nijland, Jun 27 '19 at 14:05
More or less `SELECT * FROM() AS alias WHERE alias.rank <= 2` — Raymond Nijland, Jun 27 '19 at 14:06
Thanks man, im not sure i understand but ill dig more on rank function — Youni Halloumi, Jun 27 '19 at 14:15
`` is BNF (Backus-Naur form) ... it is one of the SELECT queries from the duplicate link... — Raymond Nijland, Jun 27 '19 at 14:52
*"What version of Mysql do you use?"* Does not really matter @AlbertoMoro the linked duplication which i have provided has answers to support MySQL 5.1 to MySQL 8, pretty sure the topicstarter is covered with that. — Raymond Nijland, Jun 27 '19 at 14:55
@RaymondNijland Hey, I tried this but it's not working... It's giving me the whole initial table so no rank is being increased to 2 or more — Youni Halloumi, Jun 27 '19 at 15:36
@RaymondNijland I've included what i attempted in the body of my question — Youni Halloumi, Jun 27 '19 at 15:41
@Arion I've included my attempt in the body, but it's returning the entire initial table so no rank is being increased to 2 or more :S Any ideas ? — Youni Halloumi, Jun 27 '19 at 15:46
Very close but you forgot to initialize the MySQL user variables which is the cause of the no filtering.. -> `CROSS JOIN(SELECT @host_rank := NULL, @current_host := NULL) AS init_user_params` should do the trick see https://www.db-fiddle.com/f/94zTjHEEQs2M2E71dmpq3V/0 — Raymond Nijland, Jun 27 '19 at 15:56
But there is a other error as you should use `ORDER BY CONCAT(host_name, drive_id), process_run_id DESC` .. -> https://www.db-fiddle.com/f/94zTjHEEQs2M2E71dmpq3V/2 then the output is correct. — Raymond Nijland, Jun 27 '19 at 16:04
@RaymondNijland Bro you are an ACE! Thank you so much! I don't quite understand it and I will study it but this has helped me tremendously. — Youni Halloumi, Jun 27 '19 at 17:32
@RaymondNijland Last question if you still have a moment, what if i only want the results between specific process_run_id ? i tried "where process_run_id IN (SELECT process_run_id FROM process_fds_run_id WHERE ts_process_start BETWEEN dateFrom AND dateTo)" but regardless where i'm putting it in the statement, I am getting a wrong answer. — Youni Halloumi, Jun 27 '19 at 18:44
*" I am getting a wrong answer."* Not sure what you mean -> https://www.db-fiddle.com/f/94zTjHEEQs2M2E71dmpq3V/3 — Raymond Nijland, Jun 28 '19 at 07:51

score 1 · Answer 1 · answered Jun 27 '19 at 17:34

SELECT host_name, drive_id, process_run_id, space_free, space_total, date_processed
FROM
(SELECT host_name, drive_id, process_run_id, space_free, space_total, date_processed,
          @host_rank := IF(@current_host = CONCAT(host_name, drive_id), @host_rank + 1, 1) AS host_rank,
          @current_host := CONCAT(host_name, drive_id)
   FROM main_fds_history_list
CROSS JOIN(SELECT @host_rank := NULL, @current_host := NULL) AS init_user_param 
   ORDER BY CONCAT(host_name, drive_id), process_run_id DESC) ranked
WHERE host_rank <= 2;

Thanks to @RaymondNijland

How to QUERY top n records of each group within a table?

1 Answers1