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

From: Connor McDonald <mcdonald.connor.cs_at_bhp.com.au>
Date: Thu, 04 Mar 1999 15:42:31 +0800
Message-ID: <36DE3967.197C@bhp.com.au>

Jonathan Lewis wrote:
>
> 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.
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
> I have a spelling chequer
> It came with my pea sea
> It plainly marques for my revue
> Miss steaks eye cannot sea
>
> I've run this poem threw it
> I'm shore your pleased to no
> Its letter perfect in it's weigh
> My chequer tolled me so

Oracle 8.i gives some nice things like order by within an inline view...

select stuff
from ( select blah blah

       from   table
       order by xyz ) 

where rownum < 10;

Performance ?.....well you can't have everything --

---

Connor McDonald
BHP Information Technology
Perth, Western Australia
"Never wrestle a pig - you both get dirty and the pig likes it..." Received on Thu Mar 04 1999 - 01:42:31 CST