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;
image_1: 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.

Advertisements
SELECT 
    *
FROM
    sales_team_emails
ORDER BY sales_person_id DESC
LIMIT 5;

Action Output Message:-

5 row(s) returned

Read More
MySQL: Error 1264 Out of range value for a column [Solved]

Output:-

image_2

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.

Read More
MySQL select first row in each group

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

Read More
Select all columns except one of a MySQL table

Output:-

image_3

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

image_4

READ MORE

We hope this article helped you with getting the last N records of a table in MySQL. Good Luck!! 
You need to add a widget, row, or prebuilt layout before you’ll see anything here. 🙂

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top