SQL Query to Display Last 5 Records from Employee Table

Summary :

Here we will be learning how to retrieve last 5 rows from a database table with the help of SQL queries.

The Different Approaches we are going to explore are :

• With the help of LIMIT clause in descending order.
• With the help of Relational operator and COUNT function.
• With the help of Prepared Statement and LIMIT clause.

Creating Database :

CREATE DATABASE geeks; /* SQL is case insensitive thus 'create database geeks;' means the same

Showing Available Databases :

SHOW DATABASES;

Using the Database : 

USE geeks;

Image showing sql queries

Now Since we have changed to geeks database, now we shall create our Employee table.

Creating an Employee table : 

CREATE TABLE (
ID INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30) NOT NULL,
PHONE INT(10) NOT NULL UNIQUE,
EMAIL VARCHAR(30) NOT NULL UNIQUE,
DATE_OF_JOINING DATE);

Image showing sql queries

NOTE: We should use VARCHAR or BIGINT as the data type for PHONE column to avoid integer overflow.

To see / describe the Employee Table :

DESC Employee;

Image showing sql queries

Adding values into the Employee table :

INSERT INTO Employee (NAME, PHONE, EMAIL, DATE_OF_JOINING)
VALUES
('Yogesh Vaishnav', 0000000001, 'yogesh@mail.com', '2019-10-03'),
('Vishal Vishwakarma', 0000000002, 'chicha@mail.com', '2019-11-07'),
('Ajit Yadav', 0000000003, 'ppa@mail.com', '2019-12-12'),
('Ashish Yadav', 0000000004, 'baba@mail.com', '2019-12-25'),
('Tanvi Thakur', 0000000005, 'tanvi@mail.com', '2020-01-20'),
('Sam', 0000000006, 'sam@mail.com', '2020-03-03'),
('Ron', 0000000007, 'ron@mail.com', '2020-05-16'),
('Sara', 0000000008, 'sara@mail.com', '2020-07-01'),
('Zara', 0000000009, 'zara@mail.com', '2020-08-20'),
('Yoji', 0000000010, 'yoji@mail.com', '2020-03-10');

Images showing sql queries

 Retrieving all data from the Employee table :

SELECT * FROM Employee;

Image showing sql queries

Now let’s retrieve the last 5 rows from the Employee table.

METHOD 1 :  Using LIMIT clause in descending order

As we know that LIMIT clause gives the no. of specified rows from specifies row. We will retrieve last 5 rows in descending order using LIMIT and ORDER BY clauses and finally make the resultant rows ascending.

Since Employee table has IDs, we will perform ORDER BY ID in our query.

SYNTAX :

(SELECT * FROM <table_name> ORDER BY <id_column> DESC LIMIT <no. of rows to retrieve>)

ORDER BY <id_column> ASC;

Example :

(SELECT * FROM Employee ORDER BY ID DESC LIMIT 5)
ORDER BY ID ASC;

Output :

Image showing sql queries

METHOD 2 :  Using Relational Operator and COUNT function.

As we know that in Employee table, rows are arranged according to IDs, we can get last 5 rows, with the help of condition as

id > (total no. of rows – no. of rows to retrieve ( here 5) )

SYNTAX :

SELECT * FROM <table_name>

WHERE 

<id_column> > (SELECT COUNT(*) FROM <table_name>) – (no. of rows to retrieve);

Example : 

SELECT * FROM Employee
WHERE 
ID > (SELECT COUNT(*) FROM Employee) - 5;

Output :

Image showing sql queries

METHOD 3 : Using SQL Prepared statement and LIMIT clause

As we know that we can retrieve any no. of rows starting from a specified row with the help of LIMIT CLAUSE as:

SELECT * FROM <table_name> 
LIMIT (row after which the data is to be retrieved), (no. of rows to retrieve)

But we can’t use subqueries nor we can use variables directly with LIMIT Clause.

Thus we will  be executing prepared statement and replace he template values at runtime.

The syntax of preparing and executing a statement is as:

SYNTAX:

PREPARE <statement_name> FROM ‘<Any valid sql query>’; /*use ? (question mark) to indicate this has to be replaced with a value while execution */

EXECUTE <statement_name> USING (optional) <template_values or parameters values>

Example : 

/*declare and initialize a variable named 'rows' (here we want to retrieve last 5 rows thus the value 5*/
SET @rows = 5;
SET @startRow = (SELECT COUNT(*) FROM Employee) - @rows; /*Row after which we want to retrieve*/
PREPARE getLastFive FROM 'SELECT * FROM Employee LIMIT ?, ?';
EXECUTE getLastFive USING @starRow, @rows;

Output :

Image showing sql queries