Sometimes, we want to retrieve the last record in each group with MySQL.
In this article, we’ll look at how to retrieve the last record in each group with MySQL.
How to retrieve the last record in each group with MySQL?
To retrieve the last record in each group with MySQL, we can use the WITH statement.
For instance, we write
WITH ranked_items AS (
SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
FROM items AS m
)
SELECT * FROM ranked_items WHERE rn = 1;
to select the rows with in the subquery with
SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
FROM items AS m
And then we name that rank_messages.
We get the row number with of each row so that we can select the ones that’s last by ordering the selected rows in the subquery in descending order by the row number.
We get the row number with the ROW_NUMBER function.
And we use WITH to name the subquery ranked_messages.
Thewn we select the last rows from the subquery with
SELECT * FROM ranked_items WHERE rn = 1;
to return the last record of each group.
Conclusion
To retrieve the last record in each group with MySQL, we can use the WITH statement.
