| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Puzzle: Selecting top highest paid workers
A copy of this was sent to "Van Messner" <vmessner_at_netaxis.com>
(if that email address didn't require changing)
On Sun, 24 Oct 1999 10:37:21 -0400, you wrote:
> 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.
>
we are always adding features to SQL with each release. In Oracle8i, release 8.1, one of the new features is the ability to order an inline view (or any subquery or view for that matter). makes "top n" queries relatively easy:
select *
from ( select * from emp order by sal desc )
where rownum <= 5;
will return:
1 select ename, sal
2 from ( select * from emp order by sal desc )
3* where rownum <= 5
scott_at_8i> /
ENAME SAL
---------- ----------
KING 5000 SCOTT 3000 FORD 3000 JONES 2975 BLAKE 2850
In prior releases this would give you:
scott_at_8.0> l
1 select ename, sal
2 from ( select * from emp order by sal desc )
3* where rownum <= 5
scott_at_8.0> /
from ( select * from emp order by sal desc )
*
>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
>>
>
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Oct 24 1999 - 10:41:46 CDT
 |
 |