Re: SQL Puzzle: Selecting top highest paid workers

[Quoted] Hello Andrey:

[Quoted]     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 [Quoted] 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 [Quoted] 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

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

     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 - 21:50:04 CEST