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