Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Select fourth largest value of an attribute

 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sureshbait
Warnings : 1

New User


Joined: 02 Feb 2007
Posts: 5
Location: Chennai

PostPosted: Sun Oct 14, 2007 6:06 pm    Post subject: Select fourth largest value of an attribute
Reply with quote

I want to select fourth largest value of an attribute

ex. salary

any one help me to do this ASD SAD
Back to top
View user's profile Send private message

dick scherrer

Site Director


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

PostPosted: Sun Oct 14, 2007 11:19 pm    Post subject:
Reply with quote

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 . . . .
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Oct 15, 2007 7:18 am    Post subject:
Reply with quote

Code:

SELECT MIN(SALARY)
FROM (SELECT SALARY
      FROM TABLE
      ORDER BY SALARY
      FETCH FIRST 4 ROWS ONLY)
WITH UR
Back to top
View user's profile Send private message
nuthan

Active User


Joined: 26 Sep 2005
Posts: 146
Location: Bangalore

PostPosted: Mon Oct 15, 2007 9:47 am    Post subject:
Reply with quote

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;
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Mon Oct 15, 2007 5:09 pm    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
sri_mf

Active User


Joined: 31 Aug 2006
Posts: 215
Location: India

PostPosted: Tue Oct 16, 2007 10:26 am    Post subject:
Reply with quote

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

Here u can replace N with 4
Back to top
View user's profile Send private message
raviprasath_kp
Warnings : 1

New User


Joined: 20 Feb 2005
Posts: 65
Location: chennai

PostPosted: Wed Oct 17, 2007 2:36 pm    Post subject:
Reply with quote

SELECT E1.SALARY FROM EMP E1 WHERE 5 =(

SELECT COUNT( DISTINCT E2.SALARY ) FROM EMP E2 WHERE
E2.SALARY <= E1.SALARY );
Back to top
View user's profile Send private message
nuthan

Active User


Joined: 26 Sep 2005
Posts: 146
Location: Bangalore

PostPosted: Wed Oct 17, 2007 2:46 pm    Post subject: Reply to: Select fourth largest value of an attribute
Reply with quote

Hi Ravi,
Have you tried the query which you have given.
Back to top
View user's profile Send private message
sri_mf

Active User


Joined: 31 Aug 2006
Posts: 215
Location: India

PostPosted: Thu Oct 18, 2007 6:15 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Fri Oct 19, 2007 5:14 pm    Post subject:
Reply with quote

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

Your query can quickly become inefficient.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Select first 2 group data vice_versa DFSORT/ICETOOL 10 Mon Jun 13, 2016 2:21 pm
No new posts CALL MACRO EXECUTE FORM - Setting the... sudarshan.srivathsav PL/I & Assembler 7 Tue Feb 09, 2016 10:32 pm
No new posts Query to select Null row on priority RahulG31 DB2 24 Thu Jan 21, 2016 5:45 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us