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.

Get the Last Record From Each Group in MySQL

To fetch the last records from each group in MySQL, we can use two methods:

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:

MySQL

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.

Using SELF JOIN to Get the Last Record From Each Group

We can use the SELF JOIN to compare each value with every other value for the same condition.

Example

The following query performs LEFT JOIN to find the record that has no lesser value and then outputs it.

SELECT m1.*
FROM MARKS m1 LEFT JOIN MARKS m2
ON (m1.subject = m2.subject AND m1.marks < m2.marks)
WHERE m2.subject IS NULL;

Output:

Using SELF JOIN

Explanation: This query uses a LEFT JOIN to compare each record in the MARKS table (m1) with others having the same subject and higher marks (m2). The result displays only records where there is no higher-mark record (m2), effectively showing the highest marks in each subject.

Using CTE and Window Functions to Get the Last Record From Each Group

In this method, we are going to make use of a Common Table Expression (CTE) and window function, ROW_NUMBER() to find the maximum record. A CTE is a temporary result that is available to us for the scope of a single query while ROW_NUMBER() is a window function that is used to provide row numbers to the records in a particular partition. We can configure the ordering as well by using the ORDER BY clause.

Example

The following query creates a CTE that contains the row number for each subject ordered by the marks field and then selects the top marks record.

WITH CTE AS (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY subject ORDER BY marks DESC) AS rn
  FROM MARKS AS m
)
SELECT * FROM CTE WHERE rn = 1;

Output:

Using CTE and Window Functions

Explanation: The query utilizes a Common Table Expression (CTE) to assign a row number to each record within each subject group based on descending marks. The final output includes only the records with row number 1 in each subject, effectively displaying the highest marks for each subject.

Conclusion

In this article, we covered how to retrieve the last record in each group in MySQL. We have discussed two different methods to go about doing this, first using CTE along with Window Functions and the other using SELF JOIN.