Re: A simple SELECT ?

From: Paul Carpenter <pc69792_at_nospam.GlaxoWellcome.co.uk>
Date: 27 May 1999 07:40:47 GMT
Message-ID: <01bea814$23db27d0$281ef091@ukd23068>

This is not guaranteed to work as the rownum restriction is evaluated prior to the order by. So normally you will get the first 10 physical records in the table in ordered sequence rather than the first 10 records in ordered sequence.
If there is an index on DELIVERY_DATE then add the INDEX_DESC hint to force the data to be returned in sorted sequence thus allowing the rownum restriction to work as expected.
The other alternative which is no where near as efficient is

select *
from Delivery
where Customer_ID = <whatever>
and 10 > (select count(d2.delivery_date)

               delivery d2
               where d2.customer_id = delivery.customer_id
               and d2.delivery_date > delivery.delivery_date)

order by Delivery_Date desc;

Paul Carpenter
Database Administrator - ICIS Group Finance IS 1st Floor Glaxo Wellcome House East, Greenford Tel: 0181 966 8074 (Internal: 711 8074)

Chris Edwards <jedwards_at_nospam.es.co.nz> wrote in article <374C9DD1.1DCE9EA5_at_nospam.es.co.nz>...
> I should think this would work:
>
> select *
> from Delivery
> where Customer_ID = <whatever> and rownum <= 10
> order by Delivery_Date desc;
>
>
>
> "ACT_at_BLUEGATE" wrote:
> >
> > It seems simple...
> >
> > I've a deliveries table with three columns: date, customer id, and
quantity.
> >
> > How can i retrieve in a query the last (in time) 10 deliveries for a
> > specified customer ?
> >
> > Achille Carette, Bluegate
> > act_at_bluegate.be
>
Received on Thu May 27 1999 - 02:40:47 CDT