Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
sureshbait
Warnings : 1

New User

Joined: 02 Feb 2007
Posts: 5
Location: Chennai

 Posted: Sun Oct 14, 2007 6:06 pm    Post subject: Select fourth largest value of an attribute I want to select fourth largest value of an attribute ex. salary any one help me to do this ASD SAD

dick scherrer

Site Director

Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

Posted: Sun Oct 14, 2007 11:19 pm    Post subject:

Hello,

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

 Quote: HOW TO GET THE 5TH MAX SALARY USING QUERY?

 Quote: Nth maximum salary

 Quote: The best optimum query to find nth highest salary

Lots of people with the same homework . . . .
stodolas

Active Member

Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

Posted: Mon Oct 15, 2007 7:18 am    Post subject:

 Code: SELECT MIN(SALARY) FROM (SELECT SALARY       FROM TABLE       ORDER BY SALARY       FETCH FIRST 4 ROWS ONLY) WITH UR
nuthan

Active User

Joined: 26 Sep 2005
Posts: 146
Location: Bangalore

 Posted: Mon Oct 15, 2007 9:47 am    Post subject: 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

Active Member

Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

 Posted: Mon Oct 15, 2007 5:09 pm    Post subject: 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: 215
Location: India

 Posted: Tue Oct 16, 2007 10:26 am    Post subject: 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
Warnings : 1

New User

Joined: 20 Feb 2005
Posts: 65
Location: chennai

 Posted: Wed Oct 17, 2007 2:36 pm    Post subject: SELECT E1.SALARY FROM EMP E1 WHERE 5 =( SELECT COUNT( DISTINCT E2.SALARY ) FROM EMP E2 WHERE E2.SALARY <= E1.SALARY );
nuthan

Active User

Joined: 26 Sep 2005
Posts: 146
Location: Bangalore

 Posted: Wed Oct 17, 2007 2:46 pm    Post subject: Reply to: Select fourth largest value of an attribute Hi Ravi, Have you tried the query which you have given.
sri_mf

Active User

Joined: 31 Aug 2006
Posts: 215
Location: India

Posted: Thu Oct 18, 2007 6:15 pm    Post subject:

 sri_mf wrote: SELECT SAL FROM EMP A WHERE N=(SELECT SAL FROM EMP B WHERE B.SAL >=A.SAL) Here u can replace N with 4

Small change in my Query

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

This will work fine..

Corect me if i m wrong
stodolas

Active Member

Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

 Posted: Fri Oct 19, 2007 5:14 pm    Post subject: sri_mf: See this link from another thread http://www.craigsmullins.com/dbu_0502.htm Your query can quickly become inefficient.
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics How to force a select query to abend neo4u DB2 5 Mon Apr 23, 2018 1:36 pm SELECT TO MULIPLE OUTFIL speermb DFSORT/ICETOOL 5 Fri Mar 23, 2018 10:44 pm XML Generate with Attribute & Ele... Log Thangavel COBOL Programming 5 Fri Jan 19, 2018 6:21 pm How to select record x+7 or x+2 based... JoAnn Kulcyk SYNCSORT 6 Tue Jan 16, 2018 10:49 pm Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us