select first row after order by mysql

Question

I have a data frame like that:

date	machine_id	colA	colB	colC
24/10/2020	001
13/09/2020	001
14/08/2021	097
12/07/2020	097
25/11/2018	231
16/09/2018	231

and I get it after a query like that:

SELECT * FROM table ORDER BY machine_id, date DESC

I would like to get online the first row for each machine, that is the most recent date, so this should be the resulting data frame:

date	machine_id	colA	colB	colC
24/10/2020	001
14/08/2021	097
25/11/2018	231

How can I achieve that?

Answer 1

Try with partitioning the machine_id column and row_number function like the below query

WITH dateframevalues AS ( SELECT val.*, ROW_NUMBER() OVER (PARTITION BY machine_id ORDER BY machine_id DESC) AS rn FROM yourtable AS val ) SELECT * FROM dateframevalues WHERE rn = 1;