MySQL Select last N rows

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!!