Open In App
Related Articles

SQL Query to Display Nth Record from Employee Table

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Report issue
Report

Here. we are going to see how to retrieve and display Nth records from a Microsoft SQL Server’s database table using an SQL query. We will first create a database called “geeks” and then create “Employee” table in this database and will execute our query on that table.

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 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);

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');

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

SELECT * FROM Employee;

Now let’s display the Nth record of the table.

Syntax :  SELECT * FROM <table_name> LIMIT  N-1,1;

Here N refers to the row which is to be retrieved.

Example : 

Let’s retrieve the 6th row from the start from the Employee table we have created.

SELECT * FROM Employee 
ORDER BY <column_name>     --column name is the name according to which the rows are to be ordered.Here it's ID.
OFFSET 5 ROWS              --since N - 1 = 6 - 1 = 5 
FETCH NEXT 1 ROWS ONLY;

Output :

Method 2: Using ROWNUM

Query:

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Employee_ID) AS RowNum
    FROM Employees
) AS sub
WHERE RowNum = 8;

Output:

microsoft sql server output


Last Updated : 26 Jun, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads