5 Ways in 5 minutes to find 2nd highest Salary Or 2nd minimum Salary

Hello friends,

Today’s post is for all new bibes going for interview and scarred of sql query question how to determine 2nd highest or 2 nd lowest salary.

Or we can say nth highest or lowest salary for this I will provide you some basic Sql suppose.

DECLARE @tblEmployeeSalary TABLE(lngEmployeeId INT, strEmpName VARCHAR(100), fltBasicSalary FLOAT)

INSERT @tblEmployeeSalary SELECT 1,’RAJAT’,345345

INSERT @tblEmployeeSalary SELECT 2,’ASHISH’,76845

INSERT @tblEmployeeSalary SELECT 3,’KAPIL’,234545

INSERT @tblEmployeeSalary SELECT 4,’KAMLESH’,74564

INSERT @tblEmployeeSalary SELECT 5,’RAVI’,56756456

INSERT @tblEmployeeSalary SELECT 6,’SHIV’,75675

INSERT @tblEmployeeSalary SELECT 7,’MONICA’,76566

INSERT @tblEmployeeSalary SELECT 8,’PIYUSH’,58776

INSERT @tblEmployeeSalary SELECT 9,’KUNAL’,345567

INSERT @tblEmployeeSalary SELECT 10,’MANISH’,76766

1) The below query is simplest query for finding 2nd minimum salary.if you want 2nd maximum salary then

you have to just change the order by fltbasicSalary desc.

SELECT MAX (fltBasicSalary)

FROM @tblEmployeeSalary

WHERE fltBasicSalary IN (SELECT DISTINCT TOP 2 fltBasicSalary

FROM @tblEmployeeSalary

ORDER BY fltBasicSalary ASC)

2) This bit complex then first one in that you have to change condition of where clause for min /max

according to your requirment. if you want nth highest or lowest salary then just replace 2 by

that particular number which you want.

SELECT MIN(fltBasicSalary)

FROM @tblEmployeeSalary e1

WHERE 2 <=(SELECT COUNT(*)

FROM @tblEmployeeSalary e2 WHERE e1.fltBasicSalary >= e2.fltBasicSalary);

3) This is simple one but restriction is it require SQL SERVER 2005 it will not work on SQL server 2000

actually in sqlserver 2005 we have Rank, Row_Number(), Dens_Rank() function by utilizing then we can ,

give a row rank or number. i am using that concept.

 

SELECT tmp.fltBasicSalary

FROM (SELECT DISTINCT fltBasicSalary ,

ROW_NUMBER()OVER (ORDER BY fltBasicSalary ASC) As intRowNumber

FROM @tblEmployeeSalary)tmp

WHERE tmp.intRowNumber = 2

4) In same way we can use Rank and dens rank

SELECT tmp.fltBasicSalary

FROM (SELECT DISTINCT fltBasicSalary ,

RANK()OVER (ORDER BY fltBasicSalary ASC) As intRowNumber

FROM @tblEmployeeSalary)tmp

WHERE tmp.intRowNumber = 2

5) This is another way of getting max or min salary by a group by statement

SELECT TOP 1 fltBasicSalary

FROM (SELECT TOP 2 fltBasicSalary

FROM @tblEmployeeSalary

GROUP BY fltBasicSalary ORDER BY fltBasicSalary ASC) AS tmp ORDER BY fltBasicSalary DESC

I hope you people like it.

if you find any problem in that feel free to ask…

enjoy SQL server.

enjoy programming

Thanks

Rajat

 

Advertisements

One thought on “5 Ways in 5 minutes to find 2nd highest Salary Or 2nd minimum Salary

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s