Re: SQL Puzzle: Selecting top highest paid workers

From: Jurij Modic <jmodic_at_src.si>
Date: Sun, 24 Oct 1999 00:00:18 GMT
Message-ID: <38123c8e.6974045_at_news.siol.net>

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

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

WHERE
  salary = -v1.sal
  AND year = 1999
ORDER BY
  v1.rnk;

      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)

---

[Quoted] The above opinions are mine and do not represent any official standpoints of my employer Received on Sun Oct 24 1999 - 02:00:18 CEST