How To Get Last Record In Each Group In MySQL?
Last Updated :
04 Apr, 2024
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 DataTo 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 FunctionsExplanation: 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.
Similar Reads
How to Select the First Row of Each GROUP BY in SQL?
Selecting the first row of each group in SQL is a common requirement in database queries, especially when working with grouped data. SQL GROUP BY queries are highly effective for organizing data into logical groups, and additional techniques like window functions help to isolate the first row from e
5 min read
How to Retrieving the Last Record in Each Group in SQL Server
In SQL Server data analysis, the need to retrieve the last record in each group is a common and essential task. Consider a scenario where you are managing a database of customer orders. Each order is associated with a unique order ID and a customer ID, and you want to identify the most recent order
4 min read
How to Get Last Records in One to Many Relationship in MySQL
MySQL is a widely used relational database management system (RDBMS) that provides a robust and scalable platform for managing and organizing data. MySQL is an open-source software developed by Oracle Corporation, that provides features for creating, modifying, and querying databases. It utilizes th
9 min read
How to SELECT Top N Rows For Each Group in SQL Server
SQL Serverâs ROW_Number() window function is often used to get the top number of rows in a group. In this article, weâll look at how to get the top N Row Counts in SQL Server using this function. Weâll provide step-by-step instructions, syntax examples, and examples to help you get the results you n
4 min read
How to Get Last Inserted ID from MySQL?
In the world of Java programming and MySQL database management systems, you might be querying your MySQL database using a JDBC connection. In this article, we will look at fetching the latest ID after an insertion. Using Java's java.sql package, we will look at some examples of how to access the las
5 min read
How to Solve Must Appear in the GROUP BY Clause in SQL
SQL error âMust Appear in GROUP BY Clauseâ is one of the most common SQL errors encountered by database developers and analysts alike. This error occurs when we attempt to run queries that include grouping and aggregation without taking proper account of the structure of the SELECT statement. In thi
5 min read
How to get ID of the last updated row in MySQL?
Many times, we require updating the data based on the last updated table id. We should write an update query in such a way that we can get the last updated ID in the update statement itself The code mentioned below has been created in a generalized sense and can be used easily just by replacing the
2 min read
How to Group by Month and Year in MySQL?
GROUP BY clause is an essential feature for data analysis. It enables users to aggregate information based on specific criteria. To group records based on day, month, and year, we can use DAY(), MONTH(), and YEAR() functions respectively. In this article, we will look at how to use MySQLâs GROUP BY
4 min read
How to Delete Duplicate Rows in MySQL?
Duplicate rows are a common problem in MySQL databases. Duplicate rows can cause problems with data accuracy and integrity. They can also make it difficult to query and analyze data.Ways to Delete Duplicate Rows in MySQLThere are a few different ways to delete duplicate rows from tables in MySQL:Usi
3 min read
How to Disable ONLY_FULL_GROUP_BY in MySQL?
In MySQL, the 'ONLY_FULL_GROUP_BY' mode ensures that queries with GROUP BY clauses are written according to SQL standards. This mode requires all columns in the SELECT statement that are not aggregated to be included in the GROUP BY clause. While this is helpful for ensuring data integrity, there ar
4 min read