Re: A simple SELECT ?

ACT_at_BLUEGATE writes in message <7i3sr8$js8$1_at_xenon.inbe.net> ...
>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
>
>

Dear Achille,

This select is not simple but we will try. Ok? This seems to work. We will use sorting side-effect of distinct option.

select DELIVERIES.DATE,CUSTOMER_ID,QUANTITY from DELIVERIES,   (select distinct -(to_char(DATE,'J')),DATE from DELIVERIES    where CUSTOMER_ID=:x) A
   where CUSTOMER_ID=:x and DELIVERIES.DATE=A.DATE and ROWNUM<=10)

Inner select gives you descending order sorted deliveries dates, Outer one just selects records from an ordered query result...

Note: if you store your dates with to-seconds precision, you can use another mask instead of Julian days, that can easily be converted to numericals...

BTW, I wonder - seems that your table doesnt have primary key? At least i couldnt mind any columns combination that would be PK...

Elias A. Kuzkin, Oracle Developer,
Corvus International LLC, Moscow, Russian Federation, dev_at_corvus.ru Received on Sat May 22 1999 - 02:27:43 CDT