Get the Last Record From Each Group in MySQL
To fetch the last records from each group in MySQL, we can use two methods:
- Using SELF JOIN
- Using CTE and Window Functions
Let’s understand these methods with examples below:
Demo MySQL Database
For this tutorial on retrieving the last record from a group in MySQL, we will use the following table in examples.
Initial Data
To create this table in your system, write the following MySQL queries:
CREATE TABLE MARKS (
id INTEGER PRIMARY KEY,
subject TEXT NOT NULL,
marks TEXT NOT NULL
);
INSERT INTO MARKS (id, subject, marks) VALUES
(1, 'math', '90'),
(2, 'english', '85'),
(3, 'science', '92'),
(4, 'math', '88'),
(5, 'english', '75'),
(6, 'science', '89'),
(7, 'math', '95'),
(8, 'english', '78'),
(9, 'science', '94'),
(10, 'math', '87'),
(11, 'english', '80'),
(12, 'science', '91');
We are now going to have a look at two different methods to find the last record in each group, in this case, find the record which has the maximum marks and the groups will be the different subjects.
How To Get Last Record In Each Group In MySQL?
In MySQL, we group the data using the GROUP BY clause. There can be a need to access these groups and get the latest record in each group, for example, tracking the last login timestamp for each user.
Knowing how to retrieve the latest record from a group is essential, as it is used in many practical applications like audit logs, revisioning, reporting, etc.
In this article, we will look at how to get the last record in each group, depending on some ordering.