Select fourth largest value of an attribute

sureshbait · New User Joined: 02 Feb 2007 Posts: 5 Location: Chennai

I want to select fourth largest value of an attribute

ex. salary

any one help me to do this ASD SAD

dick scherrer · Posted: Sun Oct 14, 2007 11:19 pm

Hello,

If you look in the db2 forum others have asked for things very similar. . .

stodolas · Posted: Mon Oct 15, 2007 7:18 am

nuthan · Posted: Mon Oct 15, 2007 9:47 am

try this one also.

SELECT MIN(SALARY) FROM TABLE WHERE SALARY IN (SELECT SALARY
FROM TABLE ORDER BY SALARY FETCH FIRST 4 ROWS ONLY)
WITH UR;

stodolas · Posted: Mon Oct 15, 2007 5:09 pm

The IN clause is redundant. You only have four rows in your return set so basically you are saying make sure the salary is in those 4 rows then give the miminum of those 4 rows. What happens if you run an explain on mine vs yours?

sri_mf · Active User Joined: 31 Aug 2006 Posts: 218 Location: India

SELECT SAL FROM EMP A WHERE N=(SELECT SAL FROM EMP B WHERE
B.SAL >=A.SAL)

Here u can replace N with 4

raviprasath_kp · New User Joined: 20 Feb 2005 Posts: 65 Location: chennai

SELECT E1.SALARY FROM EMP E1 WHERE 5 =(

SELECT COUNT( DISTINCT E2.SALARY ) FROM EMP E2 WHERE
E2.SALARY <= E1.SALARY );

nuthan · Posted: Wed Oct 17, 2007 2:46 pm

Hi Ravi,
Have you tried the query which you have given.

sri_mf · Active User Joined: 31 Aug 2006 Posts: 218 Location: India

stodolas · Posted: Fri Oct 19, 2007 5:14 pm

sri_mf: See this link from another thread
www.craigsmullins.com/dbu_0502.htm

Your query can quickly become inefficient.