AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=1885&pId=-1
Selecting Records Based on Row Number in SQL
page
by Aamod Thakur
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 DESCAS 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 DESCAS 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.

 



©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-12-08 8:17:36 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search