SQL Query to Display Nth Record from Employee Table

In database management, retrieving the Nth record is a common operation, especially when dealing with large datasets. Whether we're debugging, analyzing specific rows, or implementing pagination, understanding how to fetch a specific record efficiently is crucial. In analysis, focusing on one particular entry provides deeper insights or helps make informed business decisions.

This article provides a detailed explanation of various methods to retrieve the Nth record from an SQL table, including practical examples and outputs to help us implement these techniques effectively. Which demonstrates how to achieve this using an Employee table, covering approaches such as using the LIMIT clause with an offset, Using OFFSET and FETCH NEXT clauses, and utilizing ROW_NUMBER() in a subquery

Why Retrieve the Nth Record?

Retrieving the Nth record from a table is a common requirement in database operations, often used in scenarios such as debugging, analysis, and pagination. Debugging allows us to isolate a specific row to verify the accuracy of your data, ensuring that the information stored is correct and consistent. Pagination, a vital feature in web and mobile applications, enables data to be displayed in manageable chunks, improving user experience. By the end, we will gain a clear understanding of these techniques, enabling efficient and effective interaction with our database, regardless of the complexity of the task.

Setting Up the Database

To demonstrate these methods, we'll first set up a database and an Employee table. This will serve as the foundation for executing the queries and understanding the retrieval of the Nth record. By creating a well-defined schema, we can replicate the examples easily and apply the techniques to our own datasets. Follow the steps below:

Step 1: Create the Database

We start by creating a database named geeks using the following command:

CREATE DATABASE geeks;

Step 2: Switch to the Database

To work with the newly created database, switch to it using:

USE geeks;

Step 3: Create the Employee Table

Next, create an Employee table with the following schema:

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

Step 4: Verify the Table

Use the DESC command to verify the structure of the table:

DESC Employee;

Step 5: Adding Data to the Employee 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, '[email protected]', '2019-10-03'),
('Vishal Vishwakarma', 0000000002, '[email protected]', '2019-11-07'),
('Ajit Yadav', 0000000003, '[email protected]', '2019-12-12'),
('Ashish Yadav', 0000000004, '[email protected]', '2019-12-25'),
('Tanvi Thakur', 0000000005, '[email protected]', '2020-01-20'),
('Sam', 0000000006, '[email protected]', '2020-03-03'),
('Ron', 0000000007, '[email protected]', '2020-05-16'),
('Sara', 0000000008, '[email protected]', '2020-07-01'),
('Zara', 0000000009, '[email protected]', '2020-08-20'),
('Yoji', 0000000010, '[email protected]', '2020-03-10');

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

SELECT * FROM Employee;

Output

Methods to Retrieve the Nth Record

Retrieving the Nth record in SQL is a common task for accessing specific data points. Various methods such as LIMIT with offset, OFFSET and FETCH, and ROW_NUMBER() enable precise and efficient row selection.

Method 1: Using the LIMIT Clause with Offset

The LIMIT clause in SQL is one of the simplest and most commonly used methods to retrieve the Nth record from a table. It allows us to skip a specified number of rows (using an offset) and fetch a specific number of rows afterward. This method is supported in many SQL dialects, including MySQL and PostgreSQL.

Syntax

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

Key Terms

• N-1: Specifies the offset to skip rows before retrieving the Nth record.
  
• 1: Ensures only one record (the Nth row) is fetched.

Example

To retrieve the 6th row from the start of the Employee table, use the following SQL query:

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

Explanation:

1. ORDER BY sorts the rows by the specified column (e.g., ID) to ensure consistency in the result set.

• LIMIT 5, 1 skips the first 5 rows and retrieves the 6th record from the ordered result set.

Method 2: Using ROW_NUMBER() in a Subquery

Using ROW_NUMBER() in a Subquery involves assigning a unique sequential number to each record based on a specified column (e.g., ID). This allows for the retrieval of any specific record by filtering where the row number matches the desired position (e.g., the 8th record).

Syntax

SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY <column_name>) AS RowNum
FROM <table_name>
) AS sub
WHERE RowNum = N;

Key Terms

• ROW_NUMBER(): Assigns a unique row number to each record based on the specified column.
  
• WHERE RowNum = N: Filters the Nth record.

Example

In this example, the subquery uses ROW_NUMBER() to assign a sequential number to each record based on the Employee_ID column.

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

Output

Explanation:

• In this example, the query assigns a sequential number to each row based on the Employee_ID column.
• The WHERE clause then filters for the record with the 8th row number, effectively retrieving the 8th record in the ordered list of employees.

Method 3: Using OFFSET and FETCH NEXT

Using OFFSET and FETCH NEXT together allows us to retrieve a specific record from a table in SQL by skipping a defined number of rows and then fetching a limited number of rows starting from that offset. The OFFSET clause helps to skip a given number of rows before the selection begins, while the FETCH NEXT clause specifies the number of rows to return from the starting point.

Syntax

SELECT * FROM <table_name>
ORDER BY <column_name>
OFFSET N-1 ROWS
FETCH NEXT 1 ROWS ONLY;

Key Terms

• OFFSET N-1: Skips rows before the Nth row.
  
• FETCH NEXT 1 ROWS ONLY: Retrieves the Nth row.

Example:

To retrieve the 6th record from the Employee table, the query uses the OFFSET and FETCH NEXT clauses to skip the first 5 rows and then fetch the next row in the ordered list.

SELECT * FROM Employee
ORDER BY ID
OFFSET 5 ROWS
FETCH NEXT 1 ROWS ONLY;

Output

Conclusion

Retrieving the Nth record in SQL is a fundamental operation with wide-ranging applications, including debugging, reporting, and pagination. This guide explored three effective methods to achieve this task: the LIMIT clause with offset, which is simple and efficient for most SQL dialects; the OFFSET and FETCH NEXT clauses, particularly suitable for ordered queries in SQL Server; and the use of ROW_NUMBER() within a subquery, which is valuable for advanced row-based filtering.

remmargorpp

Follow

Improve

Article Tags :

• SQL
• Databases
• SQL-Query