Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORDER BY + ROWNUM error

Re: ORDER BY + ROWNUM error

From: Dino Hsu <dino1_at_ms1.hinet.net>
Date: Sat, 30 Jun 2001 10:48:33 +0800
Message-ID: <dveqjtkgd4a5fivavi6ebp13gan8p68qkn@4ax.com>

Mark and Dion have made great comments about top-n questions. The real world question which inspires me of this is from a marketing user, say Jenny, who needs a list of top-5000 dealers who bought the most units of a specifc set of products. These dealers will get a free gift delivered along with their next order, if possible. The 5000 here is not 'exactly 5000', but 'approximately 5000'. Moreover, a lot of people will share the same 'units', and Jenny doesn't give a criterium to break the ties, so 'random' will be the rule. However, Jenny also wishes the 5000 winners will be evenly distributed to 100 zones (a higher level of sales organization), and this depends on 'luck', I would say. I might have to suggest a criterium to break the ties when they are not 'evenly enough'. NULL values have to be carefully suppressed as well, the units can be NULL when the record is a returned one. In short, most users are not technically or mathmatically sophisticated, good patience and communication are crucial.

A similar situation happens in tournaments using Swiss System. Swiss System is introduced to help when there are too many participants to have a Round-Robin tournament (everyone vs. everyone). For example, there are 16 players/teams, it takes 120 games to have a (15-round) Round-Robin, but it takes only 40 games to have a 5-round Swiss System. However, fewer games will make more probable having ties, and ties must be somehow broken to make the tournament meaningful, especially for the top-n players/teams. Therefore, the organizer must define a potentially complicated tie-breaking rule (major points first, first-level minor points next, second-level minor points, etc.) before every tournament begins. When the rules are clearly defined, Oracle will do the rest, I am sure. :-)

Dino

On 29 Jun 2001 10:03:43 -0700, roguedood_at_hotmail.com (Sean M. Dillon) wrote:

>These queries answer the question only if the top three salaries are
>unique, though. What should the answer to the question be given the
>following dataset:
>
>SQL> select empno, ename, sal
> 2 from emp
> 3 order by sal desc
> 4 /
>
> EMPNO ENAME SAL
>---------- ---------- ----------
> 7369 SMITH 7000
> 7499 ALLEN 7000
> 7521 WARD 5000
> 7839 KING 5000
> 7788 SCOTT 3000
> 7902 FORD 3000
> 7566 JONES 2975
>
>If you want the top three paid individuals, there is no right answer.
>The 3d and 4th place come in for a tie. If you want the people who
>make the top three amounts of money, you would need SMITH, ALLEN,
>WARD, KING, SCOTT, and FORD. In that case, the query is wrong.
>
>Yes, there's a point to my post ;-). Just be careful about the
>questions asked of you. Assuming we know the answers when in fact
>there is no way to assume what the question is really asking for
>because it's AMBIGUOUS.
>
>A NEW & IMPROVED WAY FOR TOP-N QUERIES:
>Depending on the version of the database you are using, you may want
>to investigate using analytic functions for this type of request
>instead of ordering. Top-N queries have always been fairly difficult
>in nature, until Analytic Functions were introduced in 817. So given
>the problem with the question, let's assume we want one of two
>answers.
>
>1. Either we want the top three salaries, and all the employees that
>make that amount, or
>2. We want the top three paid individuals... with the understanding
>that there could be a 10-way tie for 3d place which could give us a
>large number of employees indeed.
>
>TOP THREE SALARIES:
>
>SQL> select dense_rank() over (order by sal desc) rank,
> 2 sal
> 3 from emp
> 4 /
>
> RANK SAL
>---------- ----------
> 1 7000
> 1 7000
> 2 5000
> 2 5000
> 3 3000
> 3 3000
> 4 2975
> 5 2850
> 6 2450
> 7 1500
> 8 1300
> 9 1250
> 10 1100
> 11 950
>
>...or more appropriately...
>
>SQL> select x.empno, x.ename, x.sal
> 2 from (select dense_rank() over (order by sal desc) rank,
> 3 empno, ename, sal
> 4 from emp) x
> 5 where x.rank <= 3
> 6 /
>
> EMPNO ENAME SAL
>---------- ---------- ----------
> 7369 SMITH 7000
> 7499 ALLEN 7000
> 7521 WARD 5000
> 7839 KING 5000
> 7788 SCOTT 3000
> 7902 FORD 3000
>
>TOP THREE EMPLOYEES (with the possibilities for ties):
>
>SQL> select rank() over (order by sal desc) rank,
> 2 sal
> 3 from emp2
> 4 /
>
> RANK SAL
>---------- ----------
> 1 7000
> 1 7000
> 3 5000
> 3 5000
> 5 3000
> 5 3000
> 7 2975
> 8 2850
> 9 2450
> 10 1500
> 11 1300
> 12 1250
> 13 1100
> 14 950
>
>...again, constrained to the top three would be...
>
>SQL> select x.empno, x.ename, x.sal, x.rank
> 2 from (select empno, ename, sal,
> 3 rank() over (order by sal desc) rank
> 4 from emp2) x
> 5 where x.rank <= 3
> 6 /
>
> EMPNO ENAME SAL RANK
>---------- ---------- ---------- ----------
> 7369 SMITH 7000 1
> 7499 ALLEN 7000 1
> 7521 WARD 5000 3
> 7839 KING 5000 3
>
>
>REGARDING GROUP BY SORTING:
>Also, be careful using a GROUP BY in your inline views for sorting.
>You're counting on a SORT-MERGE-JOIN to be performed by the SQL
>parser, when in fact that may not happen. If the query plan does not
>use a SORT-MERGE-JOIN, your results will be wrong, as they will not be
>ordered properly.
>
>I hope that helps!
>
>_smd_
>
>
>
>markg_at_mymail.tm (MarkyG) wrote in message news:<ab87195e.0106290152.61041227_at_posting.google.com>...
>> Your experiments are indeed correct! You cannot have an order by in
>> an inline view pre 8i.
>> Its a well known 'feature'.
>> Try...
>>
>> SELECT COUNT(distinct b.empno) cnt, a.empno, a.sal
>> FROM
>> (SELECT empno, sum(sal) sal FROM emp GROUP BY empno) a,
>> (SELECT empno, sum(sal) sal FROM emp GROUP BY empno) b
>> WHERE a.sal <= b.sal
>> HAVING COUNT(distinct b.empno) <=3
>> GROUP BY a.empno, a.sal
>> ORDER BY COUNT(distinct b.empno)
>>
>> SQL>
>>
>> CNT EMPNO SAL
>> ---------- ---------- ----------
>> 1 104 124435
>> 2 103 72746
>> 3 102 62347
>>
>> HTH
>>
>> Mark
>>
>> <snip>
>> >
>> >
>> > Result from my experiments: it works in Oracle8i, but not in Oracle8:
>> >
>> > 1 select empno,sal
>> > 2 from (select empno, sal from emp order by sal desc)
>> > 3* where rownum<=3
>> > SQL> /
>> > from (select empno, sal from emp order by sal desc)
>> > *
>> > ERROR at line 2:
>> > ORA-00907: missing right parenthesis
>> >
>> > Dino
Received on Fri Jun 29 2001 - 21:48:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US