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.
