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

Here's a quick and very processor intensive solution to your problem for one staff-id at a time....

select *
from region_log r1
where staff_id_no = &staffid
and 10 >

  select count(*)
  from region_log r2
  where r2.staff_id_no = r1.staff_id_no   and r2.timestamp > r1.timestamp )

This should select the last 10 transaction for the given staff-id.... eventually!

I hope this might give you some ideas on how to solve your problem.

Just my two cents worth....

Graham

Werner wrote in message <36DD26A9.74BA_at_face.co.zackityspam>...
>Consider table region_log:
> Name Null? Type
> ------------------------------- -------- ----
> AREA_CDE NOT NULL CHAR(4)
> TIMESTAMP NOT NULL DATE
> MODULE NOT NULL VARCHAR2(48)
> STAFF_ID_NO CHAR(13)
> ACTION VARCHAR2(48)
> REMARKS VARCHAR2(256)
>
>
>This is a log file.
>I use a select statement to see what is going on today by saying...
> select *
> from region_log
> where timestamp > sysdate-1
> order by area_cde, staff_id_no, timestamp
>
>But I would very much like to only see the last 10 transactions done by
>each staff_if_no.
>
>I thought there should be some easy way to do this. Bust everytime I
>attempt
>it the SQL statement grow so big that I give up on it.
>
>I was hoping for something like:
>select *
> from region_log r1
> where r1.timestamp = (Select max(timestamp)
> from region_log r2
> where timestamp > sysdate-1
> group by staff_id_no)
>
>But this cause error 1427:Single row subquery returns more than one
>row..
>Which I can understand.
>Is there an easy way of finding the last entry for each staff_id_no?
>
>And after finding the last entry , finding the 9 before that ?
>
>Thanks
>Werner
>
>PS:Sorry aboyt the subject heading : couldn't think of anything better.
Received on Mon Mar 15 1999 - 12:55:55 CST