Re: ORDER BY + ROWNUM error

Sean,

Your comments are fine and correct. However some of the features you used such as Rank are 8i and will not work in previous Oracle versions. I can't remember what version the original poster was using but if he couldn't do the order by in an inline view, it couldn't have been 8i.

M

roguedood_at_hotmail.com (Sean M. Dillon) wrote in message news:<257c4951.0106290903.237e0fe0_at_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.
>
> 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 Mon Jul 02 2001 - 03:14:27 CDT