Re: Subquery with rownum and order by

On Oct 16, 1:29 am, Don69 <vaillancourt...._at_gmail.com> wrote:
> 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

--
**********************************************************************************
-- I think this qry will work on your model also..
--
**********************************************************************************

------------------------------------------------------------------------------------------------
-- Following query will retrive sum of top 2 salaries from each firm.
------------------------------------------------------------------------------------------------
select deptno,sum(sal) from emp a
where sal in (select sal
	  	  	 from
	  	  	 (select deptno,sal
			 from emp
			 order by deptno,sal desc) b
			 where b.deptno  = a.deptno
			 and rownum < 3
			 )
group by deptno;

Regards,
Ajit Bakshi.