Subquery with rownum and order by

From: Don69 <vaillancourt.don_at_gmail.com>
Date: Mon, 15 Oct 2007 20:29:03 -0000
Message-ID: <1192480143.364260.283490@v29g2000prd.googlegroups.com>

I'm sure this question has been asked several times, but I can't find the answer anywhere.

I need to perform this classic query:

select *
(

select salary
from employee
order by salary desc
)
where rownum < 20

within a subquery such as shown below which should return the sum of the top 20 salaries from each firm.

select firm,
(select sum(salary)

   from ( select salary

                 from employee a
                where a.firm_id = b.firm_id
                   order by salary desc  )

where rownum < 20
group by salary) as top_salaries
from employee b

Is this even possible?

Thanks Received on Mon Oct 15 2007 - 15:29:03 CDT