Monday, March 10, 2008

Find 3rd max salary

Count() will give the numer of non-null values in sxp.

Select salary from sal s
Where 2=(select count(8) from sal s1 where s.salary
The same co-related query cabn be used to find the nth max salary the number with the where clause should be n-1 for .

4 comments:

Davinder Singh said...

I didn't get this query yet. plz justify with example.

Davinder Singh said...

The correct syntax is listed below:
SELECT name,sal FROM emp e1 Where n=(select Count(distinct(sal)) From emp e2
WHERE e2.sal>=e1.sal)

Here n stands for nth highest salary. CHEERS.

satya said...
This comment has been removed by the author.
satya said...

select top 1 sal from (select distinct top 3 sal from table order by sal desc ) a
order by sal