Re: Get 10 latest customers

Madhavi Lokam (gmadhavi_at_pms991.pms.ford.com) wrote:
: In article <3oqqp4$6as$1_at_mhadf.production.compuserve.com>, Frank Greene <74200.427_at_CompuServe.COM> writes:
: |> Try
: |> select * from booking
: |> where rownum <= 10
: |> order by transaction_date;
 

: This does not work Frank.
 

: You got to do the following,
 

: 1) create a view
 

: Create view my_booking as
: select transaction_date,.......
: FROM booking
: group by transaction_date,......;
 

: This view orders the output by transaction date
 

: 2) select from the view
 

: select transaction_date,..... from my_booking
: group by transaction_date,.....
: having rownum > (select count(*)-10 from my_booking);
 

: This gives the latest 10 customers by transaction date.
 

: Madhavi LOkam
: Consultant
: Ford Motor Comapany

--
Yours does not work neither Madhavi.  First, grouping by a DATE type column transaction_date will not 
gain you much unless you are intending to group transactions down to the same second.  Secondly, 
multiple customers can have transactions on the same day and the last 10 transactions can be done by 
the same customer.  You can get the latest ten customers by the following PL/SQL.

SQLPLUS> set serveroutput on 
SQLPLUS> declare
            cursor c_booking is 
               select customer_code, max(transaction_date) 
               from booking group by customer_code order by max(transaction_date) desc;
            v_customer_code booking.customer_code%type;
            v_transaction_date booking.transaction_date%type;
         begin
            open c_booking;
            for ctr in 1..10 loop
               fetch c_booking into v_customer_code, v_transaction_date;
               dbms_output.put_line (v_customer_code||' '||v_transaction_date);
            end loop;
            close c_booking;
         end;
         .
         /
======================================================
Simon Leung                     scleung_at_us.oracle.com
DBA, Oracle Corp.               Phone: (415) 506-3096 
Disclaimer: my opinions only, not my employer's
======================================================