Re: SQL Puzzle: Selecting top highest paid workers

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 24 Oct 1999 17:01:34 GMT
Message-ID: <7uve1e$s7q$1_at_news.seed.net.tw>


Van Messner <vmessner_at_netaxis.com> wrote in message news:bkoQ3.32797$E_1.1768886_at_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.

Why did you use such a complicate condition? You just listed the record with year=1999. Received on Sun Oct 24 1999 - 19:01:34 CEST

Original text of this message