Please help

[adasoft]

How can I get the second highest salary of a department's employees with hte help of a single query?

 

[TheGreenOne]

Try this:

select max(salary)
from tablename
where salary < (
     select max(salary)
     from tablename)

 

[adasoft]

Thanx for the response.Can u extend this query to get the N'th highest salary of employees of a dept.

With regards,

 

[zhavic]

For example this returns the 5th salary
You can change the number in red to get N'th salary.
( but this query will run slowly with high 'N' )

SELECT TOP 1 salary
FROM (
SELECT TOP 5 salary
FROM tablename
GROUP BY salary
ORDER BY salary
) AS Top_n
ORDER BY salary DESC

But you can't use variable instead of number in red.
To do that you need to use dynamic query:

DECLARE @n integer
SET @n = 5

EXECUTE ( 'SELECT TOP 1 salary
FROM (
SELECT TOP ' + STR( @n ) + ' salary
FROM tablename
GROUP BY salary
ORDER BY salary
) AS Top_n
ORDER BY salary DESC' )



Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[adasoft]

Thanks Zhaivc but the query you have written will always return the top salary of the result returend by the subquery.I want n'th salary in outer query.If any idea then it will be welcomed.

With regards

 

[zhavic]

Hmmm, I think it may work.

Example - let's say you have this values:

Salary
5
20
13
100
7
4
32
18
9


So, this part of my query:
SELECT TOP 5 salary
FROM tablename
GROUP BY salary
ORDER BY salary
) AS Top_n

return this values:
100
32
20
18
13


and than this part:

SELECT TOP 1 salary
FROM (
...
) AS Top_n
ORDER BY salary DESC

returns TOP 1 value of that five values returned by subquery, but in DESCending order, so it gets value 13,
which is the 5th top value

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[karbon]

As zhavic said...His query works.
dont forget to add DESC to the inner query


zhavic
------------------
SELECT TOP 1 salary
FROM (
SELECT TOP 5 salary
FROM tablename
GROUP BY salary
ORDER BY salary
desc
) AS Top_n
ORDER BY salary DESC

or


-------------
mine same
-------------
SELECT min(lFiyat)
FROM (
SELECT TOP 5 lFiyat
FROM tbStokFiyati
GROUP BY lFiyat
ORDER BY lFiyat
DESC
) as Top_n