Re: SQL Puzzle: Selecting top highest paid workers

    I don't have a version 7 or 8 here to play with but I'm surprised. What has Oracle changed in version 8i in terms of basic SQL queries? I can understand different optimizations and so on, but I hadn't heard that they'd altered what was acceptable in plain old SQL.

Van

Jurij Modic <jmodic_at_src.si> wrote in message news:38123c8e.6974045_at_news.siol.net...
> 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
> 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)
> ================================================
> The above opinions are mine and do not represent
> any official standpoints of my employer
>
Received on Sun Oct 24 1999 - 09:37:21 CDT