This article will be looking into how to SELECT the last ‘n’ records from a MySQL table. We will be going through examples to demonstrate the concept.
Let us get started by making the sample data to be used across the examples. Create a table named sales_team_emails, followed by inserting some rows into it.
# create the table sales_team_emails
CREATE TABLE sales_team_emails (
sales_person_id INT AUTO_INCREMENT,
sales_person_name VARCHAR(255),
sales_person_email VARCHAR(255),
PRIMARY KEY (sales_person_id)
);
# Insert rows to table sales_team_emails
INSERT INTO sales_team_emails (sales_person_name,sales_person_email)
VALUES("Aditi","[email protected]"),
("Furan T","[email protected]"),
("Veronica Hedge","[email protected]"),
("Atharv","[email protected]"),
("Erick","[email protected]"),
("Rasmus","[email protected]"),
("Aditi Sharma","[email protected]"),
("Furan T","[email protected]"),
("Veronica Longman","[email protected]"),
("Simon Rappid","[email protected]");
To view the snapshot of the table sales_team_emails, execute:
SELECT * FROM sales_team_emails;
Example1: Get the last 5 records from the table sales_team_emails
We need to retrieve ‘N'(five in this example) records. Observe the below query for the solution.
SELECT
*
FROM
sales_team_emails
ORDER BY sales_person_id DESC
LIMIT 5;
Action Output Message:-
5 row(s) returned
Output:-
Explanation:-
The output in image_2 shows that the last 5 rows of the table are returned. Here we will are using the LIMIT clause. LIMIT clause is used when we want to restrict the result set to a certain number of rows. Hence to narrow the number of records to 5 we added LIMIT 5 in the select statement.
Though we got the desired result, these results are not in their creation sequence in the table. To get the results in the order of sales_person_id, we can modify the query as below:
SELECT
*
FROM
(SELECT
*
FROM
sales_team_emails
ORDER BY sales_person_id DESC
LIMIT 5) lastNrows_subquery
ORDER BY sales_person_id;
Action Output Message:-
5 row(s) returned
Output:-
Explanation:-
The output in image_3 shows that the last 5 rows of the table are returned, maintaining the insertion order. Here, the query includes an inner query to get the last 5 records from the table sales_team_emails using the LIMIT clause in the select statement. Finally, we are again selecting all the details from the sub-query results in the outer query. The result set is in an ascending order of sales_person_id using the ORDER BY clause.
Example2: Get last 3 records from the table sales_team_emails
Observe the below query for the solution.
SELECT
*
FROM
(SELECT
*
FROM
sales_team_emails
ORDER BY sales_person_id DESC
LIMIT 3) lastNrows_subquery
ORDER BY sales_person_id;
Action Output Message:-
3 row(s) returned
Output:-
READ MORE
- MySQL select last record of table
- MySQL select rows by range
- MySQL select TOP 10 rows
- MySQL select row with max value
We hope this article helped you with getting the last N records of a table in MySQL. Good Luck!!