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

I think this is a known and well discussed problem. There is no simple solution. SQL is really a set processing language and doing this sort of thing always stretches it to it's limits.

There was a thread sometime ago (last month) that discussed this problem and someone gave a URL to a site which gave the best solutions depending on the depth the query needed to go. I believe none of the answers are elegant though.

Of course I may be wrong. I'll watch and wait. If the URL reappears I shall log it this time.

Werner wrote:

> 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 Wed Mar 03 1999 - 08:09:55 CST