Selecting Records Based on Row Number in SQL
page 1 of 1
Published: 06 Oct 2009
Unedited - Community Contributed
Abstract
Selecting a record from a database table in SQL based on a certain ordering of the records in a table is a difficult problem. For example selecting the employee with the second highest salary. In this short snippet, Aamod recounts his having been asked this question in an interview and how to solve the problem.
by Aamod Thakur
Feedback
Average Rating: 
Views (Total / Last 10 Days): 41398/ 83

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.

 



User Comments

Title: Problem   
Name: Sheetal Bodhale
Date: 2012-09-27 6:05:40 AM
Comment:
Its really good..i searched alot , but this is very good solution
Title: Very Good   
Name: vijay bobbala
Date: 2011-06-10 4:12:48 AM
Comment:
really very good and simple piece of code
Title: RE: Does not work in all DB   
Name: Aamod Thakur
Date: 2009-10-13 8:16:21 AM
Comment:
Can u please mention some examples on which it does not work?
Title: Does not work in all DB   
Name: Ben
Date: 2009-10-13 4:55:51 AM
Comment:
ROW_NUMBER() is not standard SQL function and does not work in many of the database systems including SQL Server.
Title: RE: Problem - MySQL ROW_NUMBER()   
Name: Aamod
Date: 2009-10-07 5:26:00 AM
Comment:
I have never really worked with MySQL so i am not sure if this comment helps you or not.

In MYSQL you will have to use "limit" .
====================================================
--Please check below queries

select @rownum:=@rownum+1 ‘SrNo’, e.*
from EmployeeDetails e, (SELECT @rownum:=0) r
order by Salary desc limit 10;

select * from (select @rownum:=@rownum+1 ‘SrNo’, e.* from EmployeeDetails e, (SELECT @rownum:=0) r
order by score desc limit 10) emp_details
where rank between 2 and 4;

let me know if it worked for you

Thanks,
~Aamod
Title: Problem   
Name: Amit Sharma
Date: 2009-10-07 4:48:05 AM
Comment:
Will it work with MySql????
Title: Selecting Records Based on Row Number in SQL   
Name: Ankit Agarwal
Date: 2009-10-07 1:21:49 AM
Comment:
hi Aamod Thakur,

Good work
It really solved my problem.
If you about Pivot and UnPivot keywords
plz demonstrate for them also
Title: Selecting Records Based on Row Number in SQL   
Name: suman
Date: 2009-10-06 11:28:38 PM
Comment:
good article..very useful thank you
Title: RE:Selecting Records Based on Row Number in SQL   
Name: Aamod Thakur
Date: 2009-10-06 5:37:23 PM
Comment:
Hi Vince,

Yes this is the way to do it (As interviewer was satisfied with this answer [:D] )

Anyways,This was just an interview question and i have been stumped with this a couple of times when i was a fresher. I wanted to demonstrate the use of ROW_NUMBER() and also how it can be use in paging which can improve the performance very much.

If two records have same salary, the row will be selected according the clustered index on the table as clustered index determines the physical structure of the table.
In case where no clustered index is present,SQL Server is free to choose any of the two records for performance reasons.

Please advice if u have any second thought on it

Thanks
~Aamod
Title: Selecting Records Based on Row Number in SQL   
Name: Vince Stack
Date: 2009-10-06 4:48:50 PM
Comment:
This is an elegant way to do this. I will use it but how to handle if two employees are tied for second with the same salary?






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


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