|
View previous topic :: View next topic
|
| Author |
Message |
chandu321
Currently Banned
New User
Joined: 27 Jun 2006
Posts: 8
|
|
|
|
| could any please let me know how to select first 3 max salary from a table |
|
| Back to top |
|
 |
bhairon singh rathore
New User
Joined: 19 Jun 2008
Posts: 91
Location: banglore
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
ajeshrn
New User
Joined: 25 Mar 2009
Posts: 78
Location: India
|
|
|
|
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 |
|
|
ajeshrn
New User
Joined: 25 Mar 2009
Posts: 78
Location: India
|
|
|
|
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 |
|
|
chandu321
Currently Banned
New User
Joined: 27 Jun 2006
Posts: 8
|
|
|
|
| Thanks all you . now the i solution. Thank u all once again |
|
| Back to top |
|
|
purnachandra Rao
Currently Banned
New User
Joined: 29 Apr 2008
Posts: 1
Location: Hyderabad
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
ajeshrn
New User
Joined: 25 Mar 2009
Posts: 78
Location: India
|
|
| Back to top |
|
|
|
|
