Thursday, November 19, 2009

How to get the 5th highest value from a table?

How to get the 5th highest value from a table?

Table:tblEmp
Fields Emp_Name,Emp_Salary

Emp_Name Emp_Salary
A 25000
B 35000
C 12000
D 50000
E 14000
F 18000
G 28000
H 50500
I 40000

From this table I want to konw who is getting 5th highest salary?


SELECT ROWNUM NUM_K, K.* FROM (SELECT ROWNUM NUM_M, M.*
FROM ( select A.EMPLID,A.EFFDT
from ( select * from ps_job where emplid = 'FHQ4' ORDER BY EFFDT DESC ) A
WHERE ROWNUM <= Variable ) M
ORDER BY EFFDT ASC ) K
WHERE ROWNUM <=1


Note :Where varaiable is the nth largest u want

No comments:

Post a Comment

Command to do active duplicate for Oracle Database

1. First login to target server 2. Validate tns connectivity between Source DB and Target DB 3. Prepare and validate space availability 4. S...