Re: Subquery with rownum and order by

On Oct 15, 4:29 pm, 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

Maybe a different approach using analytical functions would work. An example:
St up a basic table:
CREATE TABLE T1 (
  FIRM_ID VARCHAR2(30),
  EMPLOYEE_ID VARCHAR2(30),
  SALARY NUMBER(12,2)); Insert repeatable data into the table:
INSERT INTO T1
SELECT
  'FIRM '||TO_CHAR(TRUNC((ROWNUM-1)/40)+1) FIRM_ID,   'EMPLOYEE '||TO_CHAR(ROWNUM) EMPLOYEE_ID,   ABS(ROUND(SIN(ROWNUM/180*3.141592)*60000,2)) SALARY FROM
  DUAL
CONNECT BY
  LEVEL<=400;

Now, let's try a basic query using DENSE_RANK: SELECT
  FIRM_ID,
  EMPLOYEE_ID,
  DENSE_RANK() OVER (PARTITION BY FIRM_ID ORDER BY SALARY) RANKING FROM
  T1
ORDER BY
  1,
  3;

...

FIRM_ID                        EMPLOYEE_ID

------------------------------ ------------------------------
----------
FIRM 9                         EMPLOYEE 335
26
FIRM 9                         EMPLOYEE 334
27
FIRM 9                         EMPLOYEE 333
28
FIRM 9                         EMPLOYEE 332
29
FIRM 9                         EMPLOYEE 331
30
FIRM 9                         EMPLOYEE 330
31
FIRM 9                         EMPLOYEE 329
32
FIRM 9                         EMPLOYEE 328
33
FIRM 9                         EMPLOYEE 327
34
FIRM 9                         EMPLOYEE 326
35
FIRM 9                         EMPLOYEE 325
36
FIRM 9                         EMPLOYEE 324
37
FIRM 9                         EMPLOYEE 323
38
FIRM 9                         EMPLOYEE 322
39
FIRM 9                         EMPLOYEE 321

Now, if you can find a way to limit this output so that only those rows with RANKING less than or equal to 20, and group by FIRM_D:

FIRM_ID                        SUM(SALARY)
------------------------------ -----------
FIRM 1                           217576.81
FIRM 10                          217575.25
FIRM 2                           921267.21
FIRM 3                          1118323.15
FIRM 4                           587920.86
FIRM 5                           104451.53
FIRM 6                           605965.57
FIRM 7                          1125449.57
FIRM 8                           907873.96
FIRM 9                           197057.14

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Oct 15 2007 - 23:12:22 CDT