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

Author Message
anshul_gugnani

New User

Joined: 02 Nov 2009
Posts: 73
Location: Mumbai

Posted: Fri Feb 12, 2010 2:14 pm    Post subject: Query to find nth max salary in table EMP

Hi,

Can Anyone please explain me the logic behind the below query to find the nth max salary in EMP table -

 Quote: select sal from emp A where n = ( select count(*) from emp B where A.sal < B.sal )

Thanks

enrico-sorichetti

Senior Member

Joined: 14 Mar 2007
Posts: 10478
Location: italy

 Posted: Fri Feb 12, 2010 2:16 pm    Post subject: Reply to: Query to find nth max salary in table EMP topic moved where it belongs!
dick scherrer

Site Director

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

 Posted: Fri Feb 12, 2010 2:28 pm    Post subject: Hello, Suggest you write down 5 "salaries" and manually step thru the process. This will quickly show what happens when the query processes.
anshul_gugnani

New User

Joined: 02 Nov 2009
Posts: 73
Location: Mumbai

 Posted: Fri Feb 12, 2010 3:21 pm    Post subject: Hi
dick scherrer

Site Director

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

Posted: Fri Feb 12, 2010 10:38 pm    Post subject: Reply to: Query to find nth max salary in table EMP

 Quote: Hi
Well, it is a start. . .
anshul_gugnani

New User

Joined: 02 Nov 2009
Posts: 73
Location: Mumbai

Posted: Fri Feb 12, 2010 11:15 pm    Post subject:

Hi, Say I have 5 salaries as -

12, 25, 13, 78, 03

According to
 Quote: select count(*) from emp B where A.sal < B.sal

each salary will have a count for which it is greater then other salaries in table.

So result for records will be this will be -

3, 1, 3, 0, 4

Now, According to
 Quote: select sal from emp A where (n-1) =

25 is salary whose count is equal to (n-1).

Please suggest if my understanding is correct. I think this co-related subquery.

Thanks!
dick scherrer

Site Director

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

Posted: Sat Feb 13, 2010 3:01 am    Post subject:

Hello,

Other than the typo (3, 1, 3, 0, 4) , i believe you have it correctly

Also - Fromthe db2 manual (i added the bold for emphasis):
 Quote: A subquery is actually executed for each row of R only if it includes a correlated reference. In fact, a subquery with no correlated references is executed just once, whereas a subquery with a correlated reference may have to be executed once for each row.
anshul_gugnani

New User

Joined: 02 Nov 2009
Posts: 73
Location: Mumbai

Posted: Sat Feb 13, 2010 3:15 pm    Post subject:

Hi,

 Quote: Other than the typo (3, 1, 3, 0, 4) ,

Could you please explain as to why this is wrong??

Thanks
dick scherrer

Site Director

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

 Posted: Sat Feb 13, 2010 3:57 pm    Post subject: Hello, Maybe 3, 1, 2, 0, 4 . . .?
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics RACF- How to find the Last access of ... rahul shanmuganatan All Other Mainframe Topics 6 Thu Jun 21, 2018 3:19 pm Rexx code to find greater then symobl... Bharath Vikraman CLIST & REXX 8 Tue May 29, 2018 9:40 am Extracting DDL for all list of intere... Ashishpanpaliya DB2 5 Mon May 21, 2018 4:08 pm How to force a select query to abend neo4u DB2 7 Mon Apr 23, 2018 1:36 pm Searching a table for the field name? socker_dad COBOL Programming 8 Sat Mar 31, 2018 2:57 am

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