Re: SQL Puzzle: Selecting top highest paid workers

From: Van Messner <vmessner_at_netaxis.com>
Date: Sat, 23 Oct 1999 15:50:04 -0400
Message-ID: <bkoQ3.32797$E_1.1768886@typ11.nn.bcandid.com>

Hello Andrey:

    I posted this solution the other night. One guy said it didn't work, everyone else ignored it. This is the solution that allows for ties. I assumed that salaries might often be the same for more than one individual and that what you wanted was to know the fifty most highly compensated people in your company. It is a general solution for ranking with a condition. The subquery assigns a rank to each salary, the outer query gives you the answer you want. I chose to make a particular year the condition.

   I got to Oracle SQL and tried this solution on an 8.1 database. It does work. The awkward lack of a column alias in the outer where clause is because Oracle has a problem accepting the inner alias. Try this solution to convince yourself.

Van

  1 create table bigbucks (
  2 name VARCHAR2(20),
  3 salary NUMBER(8,2),
  4* year NUMBER(4))

Table created.

SQL> insert into bigbucks values ('JOE',  5000, 1999);
SQL> insert into bigbucks values ('JACK', 4000, 1999);
SQL> insert into bigbucks values ('JOHN', 2500, 1999);
SQL> insert into bigbucks values ('JERRY',3000, 1999);
SQL> insert into bigbucks values ('JUNE',4000,1999);
SQL> insert into bigbucks values ('JULIE',1500,1998);
SQL> insert into bigbucks values ('JUAN',2500,1998);
SQL> insert into bigbucks values ('JIM',1000,1999);

SELECT
  sal1.year, sal1.salary,
  (SELECT
     COUNT(DISTINCT sal2.salary)
   FROM
     bigbucks sal2
   WHERE

     sal2.salary >= sal1.salary
     and 1999 = sal1.year) rnk

FROM
   bigbucks sal1
WHERE (SELECT

         COUNT(DISTINCT sal2.salary)
       FROM
         bigbucks sal2
       WHERE
         sal2.salary >= sal1.salary
         and 1999 = sal1.year) > 0

ORDER BY
  rnk;

     YEAR    SALARY       RNK
     --------- --------- ---------
     1999      5000         1
     1999      4000         2
     1999      4000         2
     1999      3000         3
     1999      2500         4
     1999      1000         6

6 rows selected.

NetComrade <andreyNSPAM_at_bookexchange.net> wrote in message news:3810b063.342650830_at_news.earthlink.net...
> Hi,
>
> I think I misrepresented my previous post.
> I need to know the top highest paid workers.
> Ot top 50 highest anything.. :) for a nonsorted table of course...
>
> Thanx
> ---------------
> Andrey Dmitriev eFax: (978) 383-5892 Daytime: (917) 373-5417
> AOL: NetComrade ICQ: 11340726 remove NSPAM to email
>
Received on Sat Oct 23 1999 - 14:50:04 CDT