On Sat, 10 Nov 2001, Amir Sadeghi wrote:
> Sorry....I was not accurate in my original problem description.
>
> In fact, what I'd like to to is to have the top 2 money makers for each
> department. Something like the following:
>
> Dept# Employee Num Salary Amount
>
> abc 12345 60K
> abc 67890 58k
> def 33333 62K
> def 12098 61k
> ghi 20485 53K
> ghi 20484 52K
Amir,
Using the rank() OLAP function with the 'partition by' option
makes this easy.
with q (dept,empnum,sal,rank) as (
select
dept,
empnum,
sal,
dense_rank() over (partition by dept order by sal desc)
from
deptsal
)
select
dept,
empnum,
sal
from
q
where
rank <= 2
order by
dept,
sal desc;
Good luck,
idb
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
