SQL Query to Display Nth Record from Employee Table

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