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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 3 Mar 1999 14:59:41 -0000
Message-ID: <920473593.28346.0.nnrp-01.9e984b29@news.demon.co.uk>

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 Received on Wed Mar 03 1999 - 08:59:41 CST