Open In App
Related Articles

SQL Query to Display Nth Record from Employee Table

Improve Article
Improve
Save Article
Save
Like Article
Like

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

Unlock the Power of Placement Preparation!
Feeling lost in OS, DBMS, CN, SQL, and DSA chaos? Our Complete Interview Preparation Course is the ultimate guide to conquer placements. Trusted by over 100,000+ geeks, this course is your roadmap to interview triumph.
Ready to dive in? Explore our Free Demo Content and join our Complete Interview Preparation course.

Last Updated : 26 Jun, 2023
Like Article
Save Article
Previous
Next
Similar Reads
Complete Tutorials