Good way to 'join' and 'max' in mysql

Question

i'm a web developper and i'm a bit lost in one of my queries which seems to be simple...

here is the point, i have a "step" table, where i can see what document a guy can upload in my site.

i ve an 'upload' table where i see what document is really uploaded on server.

Here is the 'step' table:

id bigint auto_increment primary key,
document_id bigint null,
etape_id int not null,
emplacement varchar(500) not null,
extension json null,
max_size int null,
creation datetime not null comment (DC2Type:datetime_immutable),
modification datetime not null,
required tinyint(1) not null,
contraintes json null

Here is the upload document:

id int auto_increment primary key,
candidature_id bigint not null,
step_id bigint null,
path varchar(255) not null,
creation datetime not null,
modification datetime not null,
status tinyint(1) null,
nom_origine varchar(255) not null

The relation[ship] between these tables are on step.id and upload.step_id

The base request i have is this :

select
  edu.id as id, ud.id as docUp, 
  ud.nom_origine as nomUD, ud.creation
from etape_document_uploade as edu
left join upload_document as ud 
  on edu.id = ud.etape_document_uploade_id and candidature_id = 12
where etape_id = 2

who gives me :

+--+-----+------------------------------------------+-------------------+
|id|docUp|nomUD |creation |

+--+-----+------------------------------------------+-------------------+
|6 |14 |confirm_mini2.png |2022-03-18 15:13:58|
|6 |20 |confirm_mini.png |2022-03-19 15:13:58|
|7 |15 |intervention_ch.png |2022-03-18 15:13:58|
|10|19 |2022-03-23 08-01-45.png|2022-03-23 11:30:28|
+--+-----+------------------------------------------+-------------------+

but what i want is to have only one occurence per 'id' having the creation max between those 2 lines so :

+--+-----+------------------------------------------+-------------------+
|id|docUp|nomUD |creation |
+--+-----+------------------------------------------+-------------------+
|6 |20 |confirm_mini.png |2022-03-19 15:13:58|
|7 |15 |intervention_ch.png |2022-03-18 15:13:58|
|10|19 |2022-03-23 08-01-45.png|2022-03-23 11:30:28|
+--+-----+------------------------------------------+-------------------+

sorry if it's not well explain or if my english is bad..... i can give u more details if needed

score 1 · Accepted Answer · answered Mar 23 '22 at 16:20

You can use ROW_NUMBER() to identify the latest row per subgroup. Then, filtering is easy.

For example:

select
from (
  select
    edu.id as id, ud.id as docUp, 
    ud.nom_origine as nomUD, ud.creation,
    row_number() over(partition by edu.id order by ud.creation desc) as rn
  from etape_document_uploade as edu
  left join upload_document as ud 
    on edu.id = ud.etape_document_uploade_id and candidature_id = 12
  where etape_id = 2
) x
where rn = 1

Thx so much ! I didn't know the `row_number` function and how it works . — MaxPtdr, Mar 24 '22 at 07:03

Good way to 'join' and 'max' in mysql

1 Answers1