Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Puzzle: Selecting top highest paid workers
On Sat, 23 Oct 1999 15:50:04 -0400, "Van Messner"
<vmessner_at_netaxis.com> wrote:
>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
Hello Van
Your solution (although I'm not sure it returns the result the original poster was looking for) works only in 8i, not in 8.0 or lower. Here is the query that returns the same result in 7.2 or higher.
SELECT /*+ rule */
year, salary, v1.rnk
FROM
bigbucks,
(SELECT
sal, rownum rnk
FROM
(SELECT DISTINCT -salary sal FROM bigbucks WHERE year=1999)) v1
YEAR SALARY RNK
---------- ---------- ----------
1999 5000 1 1999 4000 2 1999 4000 2 1999 3000 3 1999 2500 4 1999 1000 5
6 rows selected.
Regards,
Jurij Modic <jmodic_at_src.si>
Certified Oracle DBA (7.3 & 8.0 OCP)