Thursday, 3 January 2013

second highest salary in sql without using top and with using top in sql server


--Second hightest Salary without using top
SELECT* FROM emp e1
WHERE
2 = (SELECT COUNT(DISTINCT (e2.sal))FROM emp e2
WHERE e2.sal >= e1.sal)

SELECT TOP 1 sal
FROM (SELECT DISTINCT TOP 2 sal FROM emp ORDER BY sal DESC) a
ORDER BY sal

second lowest salary in sql without using top and with using top

--Second lowest Salary
SELECT * FROM emp e1
WHERE
2 = (SELECT COUNT(DISTINCT (e2.sal))FROM
emp e2 WHERE e2.sal <= e1.sal)

SELECT TOP 1 sal
FROM (SELECT DISTINCT TOP 2 sal FROM emp ORDER BY sal ) a
ORDER BY sal DESC
Just change the number u can get n th salary for Employee

with cts as
(
select Name,rn=ROW_NUMBER() over (order by Salary desc) from SalaryTable 
)
select Name from cts where rn in (2,5)



No comments:

Post a Comment