How to retrieve the last record in each group with MySQL?

Spread the love

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.

Leave a Reply

Your email address will not be published.