Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Can this be done in SQL ?
select * from list t1
where t1.date < :date
and n < ( select count(distinct t2.date) from list t2 where t2.date >
t1.date;
where "n" is the number of ocurrences - 1.
I hope this help.
Alex
Matt Randle <matt_at_imat.demon.co.uk> wrote in message
news:929384284.13894.0.nnrp-08.d4e48d0d_at_news.demon.co.uk...
> If the results of a query return a list of items sorted by date, is there
> anyway of selecting the last 'n' items from the list before a given date.
> For example, if the results of the query are,
>
> Item ID Date
> ===== ====
>
> 1 1/1/99
> 2 1/4/99
> 3 1/8/99
> 4 1/10/99
> 5 1/12/99
>
> An example query might be to return the ID's of the 2 items with a date
> prior to 1/9/99 which should return ID's 2 and 3.
>
> Can this be done in one SQL statement ??
>
> Thanks,
>
> Matt.
>
>
Received on Mon Jun 14 1999 - 15:55:48 CDT