SQL Query to Display Last 50% Records from Employee Table

Here, we are going to see how to display the last 50% of records from an Employee Table in MySQL and MS SQL server’s databases.

For the purpose of demonstration, we will be creating an Employee table in a database called “geeks“.

Creating a Database :

Use the below SQL statement to create a database called geeks:

CREATE DATABASE geeks;

Using Database :

USE geeks;

Table Definition:

We have the following Employee table in our geeks database :

CREATE TABLE Employee(
ID INT IDENTITY(1,1) KEY,     --IDENTITY(1,1) tells start ID from 1 and increment
                            -- it by 1 with each row inserted.
NAME VARCHAR(30) NOT NULL,
PHONE INT NOT NULL UNIQUE,
EMAIL VARCHAR(30) NOT NULL UNIQUE,
DATE_OF_JOINING DATE);

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

You can use the below statement to query the description of the created table:

EXEC SP_COLUMNS Employee;

Adding Data to Table:

Use the below statement to add data to 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'),
('Rekha Vaishnav', 12, 'rekha@mail.com', '2021-03-25');

To verify the contents of the table use the below statement:

SELECT * FROM Employee;

Now let’s retrieve the last 50% of the records from the Employee Table.

For MS SQL database :

In MS SQL we can directly retrieve the last 50% of the records with the help of top and percent and order by clauses. A simple syntax for the same is given below:

Syntax :

select * from 

  /*Gives the top N percent records from bottom of a database table*/

(select top N percent * from <table_name> order by <column_name> desc)<identifier>

order by <column_name>;

Example :

SELECT * FROM
(SELECT top 50 percent * FROM Employee ORDER BY ID DESC)
ORDER BY ID;

Output :