| sql query [message #7471] |
Mon, 16 June 2003 22:50  |
saravanan
Messages: 70
Registered: October 2000
|
Member |
|
|
hi anybody help me,
query for to find the second max salary from employee table or nth max salary.
how to write.
regards,
sarav
|
|
|
|
| Re: sql query [message #7472 is a reply to message #7471] |
Mon, 16 June 2003 23:28  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
Please use the search on this forum and the SQL forum. This question has been asked and answered numerous times.
|
|
|
|
| Re: sql query [message #7474 is a reply to message #7471] |
Tue, 17 June 2003 00:51 |
Shailender Mehta
Messages: 49
Registered: June 2003
|
Member |
|
|
Table : Temp
EMPID SAL
--------- ---------
1 1000.15
2 2000.11
3 1001.12
4 35000.12
4 2400.24
5 5876.22
6 8763.22
7 87654.11
9 8765.23
Select empid, sal, Rk
From(
Select 'x', empid, sal,
Rank() Over (Partition By 'x' Order By sal Desc) as rk
from ( Select a.empid, a.sal
From temp a
where 3 > ( Select count(*)
from temp b
Where b.sal > a.sal
)
Order by a.sal desc
) temp
Order By 4 Asc
)
Where Rk = &No
/
Enter value for no: 1
old 15: Where Rk = &No
new 15: Where Rk = 1
EMPID SAL RK
--------- --------- ---------
7 87654.11 1
old 15: Where Rk = &No
new 15: Where Rk = 2
EMPID SAL RK
--------- --------- ---------
4 35000.12 2
old 15: Where Rk = &No
new 15: Where Rk = 3
EMPID SAL RK
--------- --------- ---------
9 8765.23 3
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
