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
 

 

to select first 3 max salary from a table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
chandu321
Currently Banned

New User


Joined: 27 Jun 2006
Posts: 8

PostPosted: Tue Oct 27, 2009 12:52 pm    Post subject: to select first 3 max salary from a table
Reply with quote

could any please let me know how to select first 3 max salary from a table
Back to top
View user's profile Send private message

bhairon singh rathore

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Tue Oct 27, 2009 1:01 pm    Post subject:
Reply with quote

Find below logic to suit your requirment

http://www.ibmmainframes.com/viewtopic.php?t=42252&highlight=
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Oct 27, 2009 1:36 pm    Post subject:
Reply with quote

v8 (if its not to be used in a subselect) or v9 :
Code:
...order by salary desc
fetch first 3 rows only

will give you 3 rows with the highest salaries.

Code:
...group by salary
order by salary desc
fetch first 3 rows only

will give you the three highest distinct salaries
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Tue Oct 27, 2009 2:14 pm    Post subject:
Reply with quote

chandu321,

Do you want the results in a single column, or do you want the results in a single row.
Way A
Code:

Result 1
----------
Max 1
Max 2
Max 3

or
Way B
Code:

val1     val2      val3
------  ------    -------
max1     max2      max3   


Is there a way we can bring the results in the second way? Just eager to know. Am not sure whether I need post this as a new topic?
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Tue Oct 27, 2009 2:16 pm    Post subject:
Reply with quote

Hi chandu321,

If you are expecting the results to be displayed as shown in the first section then you can do an order by and fetch first 3 rows only as suggested by GuyC.
Back to top
View user's profile Send private message
chandu321
Currently Banned

New User


Joined: 27 Jun 2006
Posts: 8

PostPosted: Tue Oct 27, 2009 2:31 pm    Post subject:
Reply with quote

Thanks all you . now the i solution. Thank u all once again
Back to top
View user's profile Send private message
purnachandra Rao
Currently Banned

New User


Joined: 29 Apr 2008
Posts: 1
Location: Hyderabad

PostPosted: Tue Oct 27, 2009 2:45 pm    Post subject: max salary
Reply with quote

select max(sal) from emp
where sal < ( select max(sal) from emp
where sal < (select max(sal) from emp ))

you can execute this query you can get first 3 sal from same table

Thanks &Regards
Purna chandrarao
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Oct 27, 2009 2:50 pm    Post subject: Re: max salary
Reply with quote

purnachandra Rao wrote:
select max(sal) from emp
where sal < ( select max(sal) from emp
where sal < (select max(sal) from emp ))

you can execute this query you can get first 3 sal from same table

Thanks &Regards
Purna chandrarao

no, you don't. You'll only get the third highest distinct salary
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Oct 27, 2009 2:51 pm    Post subject:
Reply with quote

Way B (the three max distinct salaries on one row) :
Code:
select * from
table (select max(a1.dbid) as mx from sysibm.sysdatabase a1) as a
join
table (select max(b1.dbid) as mx from sysibm.sysdatabase b1 where b1.dbid < a.mx) as b on 1=1
join
table (select max(c1.dbid) as mx from sysibm.sysdatabase c1 where c1.dbid < b.mx) as c on 1=1


or if you like recursive sql (also good for solving way A or any other problem where you need rowcount in V8):
Code:
with tb1(lvl,mx) as
(select 1,max(dbid) from sysibm.sysdatabase 
union all
  select lvl+1, (select max(dbid) from sysibm.sysdatabase a where a.dbid < b.mx )
     from tb1 b where lvl < 3)
-- select * from tb1
 select
 sum(case when lvl = 1 then mx else 0 end) as mx1,
 sum(case when lvl = 2 then mx else 0 end) as mx2,
 sum(case when lvl = 3 then mx else 0 end) as mx3
 from tb1
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Tue Oct 27, 2009 3:08 pm    Post subject:
Reply with quote

Thank you GuyC icon_cool.gif icon_biggrin.gif icon_biggrin.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    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 Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm


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