Re: how to find "bottom" of table in a group query

From: Mike Burden <michael.burden_at_capgemini.co.uk>
Date: Thu, 04 Mar 1999 09:48:42 +0000
Message-ID: <36DE56F8.8839C7BA@capgemini.co.uk>

If I've found the correct solution you refer to, it will not work for this case. This solution will only work for single groups. This example requires Top X for multiple groups.

I like Jurij's solution to get a sort before the rownum filter. This raises some interesting ideas which have probably been discussed before but I will raise them again now just in case.

I know it can't be guaranteed that the GROUP BY clause returns results in sorted order, but it does and, if you willing to take the risk, it can be used to force a sort before the rownum filter. This can be used to improve the readability of Jurij's query:

e.g.

Jurij's solution

SELECT a.job, a.sal FROM

  2     (SELECT job, SUM(sal) sal FROM emp GROUP BY job) a,
  3     dual

  4 WHERE -1*sal = DECODE(dual.dummy(+),'X',0,0)   5 AND ROWNUM <= 3
  6 ORDER BY a.sal DESC;

Is this an improvement???

SELECT job,sal FROM

      (SELECT job, -(-sal) from
           (
              SELECT job,sum(sal) as sal from emp group by job
           )
          group by -sal,job           -- Just used to sort return set.
Note if 8i supports order by clause here then use this instead.
       )
       where ROWNUM <= 3;

Note the -(-sal) must be coded as Oracle rejects sal. Interesting weakness in the optimizer / parser here. This problem gives some indication as to how the optimizer / parser is working in this complex area.

So the only limitation of group by as a sort is how to get desending for character strings???

Matthias Gresz wrote:

> Jonathan Lewis schrieb:
> >
> > For details on the last possible timestamp
> > per staff_id_no, the following should work:
> >
> > select *
> > from region_log r1
> > where (r1.staff_id_no, r1.timestamp) in
> > (Select r2.staff_id_no, max(r2.timestamp)
> > from region_log r2
> > where r2.timestamp > sysdate-1
> > group by r2.staff_id_no
> > )
> >
> > As others have pointed out,though, there is no
> > 'nice' solution to general 'bottom 10'/'top 10' problem.
> >

>

> I disagree:
> check out dejanews archive for Jurij Modics TOP X solution posted here
> last year!

>

> HTH
> Matthias
> --
> grema_at_t-online.de

>

> Es gibt nichts Neues mehr.
> Alles, was man erfinden kann, ist schon erfunden worden.
> Charles H. Duell, Leiter des US Patentamtes bei seinem Rücktritt 1899
Received on Thu Mar 04 1999 - 03:48:42 CST