Tell me the best optimum query to find nth highest salary

itssrikanth4u · New User Joined: 21 Nov 2004 Posts: 9

Hi,

Can any one please tell me the best optimum query to find nth highest salary?

bye
thanx in advance
take care
srikanth

ovreddy · Posted: Mon Dec 13, 2004 2:47 pm

Hi,

Here is a way in which we can find nth max or top n max salaries from emp table...

SELECT SAL FROM EMP A WHERE N<=(SELECT COUNT(DISTINCT SAL) FROM EMP B WHERE A.SAL<=B.SAL);

This is one way to find top n salaries. But execute this and check EXPLAIN results so that u will get exact optimization. If you have any other queries an your mind apply Explain on them and compare... ok

Bye,
Reddy.

jz1b0c · Posted: Mon Dec 13, 2004 11:56 pm

Srikanth,
This was answered earlier,

SELECT *
FROM TEST.TABLE1 T1
WHERE 5 = ( SELECT COUNT(DISTINCT T2.NUM)
FROM TEST.TABLE2 T2
WHERE T2.NUM >= T1.NUM)
ORDER BY NUM DESC ;;