ASPAlliance: Articles, reviews, and samples for .NET DevelopersURL:http://aspalliance.com/articleViewer.aspx?aId=1885&pId=-1
 Selecting Records Based on Row Number in SQL
 page
by
Feedback
Average Rating:
Views (Total / Last 10 Days): 40403/ 45

Problem Description

At an interview I was asked, how can we select 2nd record from a EmployeeDetails table with highest salary. the table schema was like

Listing 1: Database Schema and Sample Data

```CREATE TABLE EmployeeDetails
(
EID int unique,
EmployeeName varchar(100),
Salary money
);

insert into EmployeeDetails values(120,'Elite Crew',25000.00)
insert into EmployeeDetails values(121,'Chuck Berry',1025000.00)
insert into EmployeeDetails values(129,'Kailash Kher',30000.00)
insert into EmployeeDetails values(135,'Abhijit Sawant',22000.00)
insert into EmployeeDetails values(009,'HeatOn',2522000.00)
insert into EmployeeDetails values(007,'James Bond',122000.00)
insert into EmployeeDetails values(123,'QuickGun MuruGun',50000.00)```

Solution

Here is the answer - We can use ROW_NUMBER() function for selecting the n-th row from the table according to the sort condition

Listing 2: Obtaining the second highest salary

```SELECT *
FROM(
SELECT ROW_NUMBER() OVER (ORDER BY Salary DESC) AS SrNo,
EId,EmployeeName, Salary
FROM
EmployeeDetails
)    AS EMPLOYEE
WHERE
SrNo=2```

This will return the second record from the table when sorted by Salary in descending order.

Similarly we can find out records between specific range.

Listing 3: Range of records

```SELECT *
FROM(
SELECT ROW_NUMBER() OVER (ORDER BY Salary DESC) AS SrNo,
EId,EmployeeName, Salary
FROM
EmployeeDetails
)    AS EMPLOYEE
WHERE
SrNo between 3 and 5```

This can be applied in case of Paging where we want to display specific range of records. This method can reduce the overhead of transferring whole table and binding it to a data control.

Kindly comment if it helps you in any way or if u have any better idea.