Re: ORDER BY + ROWNUM error

From: Sean M. Dillon <roguedood_at_hotmail.com>
Date: 29 Jun 2001 10:03:43 -0700
Message-ID: <257c4951.0106290903.237e0fe0@posting.google.com>

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.

Either we want the top three salaries, and all the employees that make that amount, or
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 - 12:03:43 CDT