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

Mike Burden schrieb:
>
> 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;
>

It's the where-clause that induces the sorting before rownum application.

Example:

Table anrufe (phone calls):

eingang date -- with date and time portion: moment when the call was received
kbnr number(4) -- number of the employe who was called

SELECT

	a.eingang, 
	a.kbnr 
FROM   
	anrufe a,
      	dual
    WHERE 
	   a.eingang = DECODE(dual.dummy(+),'X',sysdate,sysdate)
    AND 
	   ROWNUM <= 10 
    ORDER BY a.eingang asc
	;

Will return the ten oldest phone calls!

Regards
Matthias
--
grema_at_t-online.de

Protect privacy, boycott Intel: http://www.bigbrotherinside.org Received on Thu Mar 04 1999 - 06:17:30 CST