Greatest N per group based on recent date in mysql query

Question

I need some kind of assistance to fix a problem, below is a table with scan data, i want my sql query to only display records with latest report_created records for a given project. A project can have multiple scan_id

Example in the below table 8888_super_start should only be displayed once with the latest report_created time and it should only show the latest number of files_scanned.

+--------------------+------------------+---------------+---------------------+---------------------+
| scan_id            | project_name     | files_scanned |  scan_submitted     |  report_created     |
+--------------------+------------------+---------------+---------------------+---------------------+
| ba2468dd-2e38-478a | 8888_super_start | 123           | 2018-01-23 12:58:43 | 2018-01-23 13:48:46 |
| 5d3cb423-4cbb-4196 | 9111_kick_start  | 1040          | 2018-01-23 14:57:15 | 2018-01-23 15:58:33 |
| 75ff4cfd-172a-4f2d | 8888_super_start | 180           | 2018-05-25 14:37:33 | 2018-05-25 20:17:19 |
+--------------------+------------------+---------------+---------------------+---------------------+

I tried the approach mentioned in the post but it is not providing proper results. Please provide some suggestions.

Attempt

select sd.scan_id,sd.project_name,sd.files_scanned from blackbox.tb_cx_scan_details sd
left outer join blackbox.tb_cx_scan_details t2
on sd.scan_id = t2.scan_id
and (sd.report_created < t2.report_created
or (sd.report_created = t2.report_created ))
where t2.scan_id is NULL

MySQL 5.7 is reaching end of life in 9 months from now. Maybe it's a good time to upgrade. — The Impaler, Jan 20 '23 at 14:10

The Impaler · Accepted Answer · 2018-11-30T20:25:18.103

4

In MySQL 5.x you can use a subquery:

select * from tb_cx_scan_details
  where (project_name, report_created) in (
    select project_name, max(report_created) 
    from tb_cx_scan_details
    group by project_name
  )

In MySQL 8.x you can use a window function:

select *,
    row_number() over (partition by project_name 
                       order by report_created desc) as my_rank
  from tb_cx_scan_details
  where my_rank = 1

edited Nov 30 '18 at 20:25

answered Nov 30 '18 at 20:20

The Impaler

45,731
9
39
76

For MySQL 8.x, you cannot filter directly on the alias of your window function (it raises an `Unkown column` error). Instead wrap the window function in a subquery and perform the filtering outside. `select *, my_rank from (select row_number() over (partition by project_name order by report_created desc) as my_rank from tb_cx_scan_details) where my_rank = 1` – ymentha14 Jan 20 '23 at 01:58

score 1 · Answer 2 · answered Nov 30 '18 at 20:26

One approach is to first group by project_name and get the latest report_created for every group, then you can join to this new table on the adequate columns for get the rest of the information for every project.

SELECT
    sd.scan_id,
    sd.project_name,
    sd.files_scanned
FROM
    blackbox.tb_cx_scan_details AS sd
INNER JOIN
    ( SELECT
          project_name, MAX(report_created) AS MaxReportCreated
      FROM
          blackbox.tb_cx_scan_details
      GROUP BY
          project_name ) AS latest ON latest.project_name = sd.project_name
                                   AND latest.MaxReportCreated = sd.report_created

Greatest N per group based on recent date in mysql query

2 Answers2