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
for more information checkout http://www.indiandotnet.wordpress.com
