Re: Get 10 latest customers

In article <3p0nca$pme_at_inet-nntp-gw-1.us.oracle.com>, scleung_at_us.oracle.com (Simon Leung) writes:
|> 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
|> ======================================================

Hey simon,

	Your solution is excellent . But i just want to correct you on one of the 
	points you gave for my solution not working. 

Your point is,

        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.

I am using group by to create the view and if the query is just selecting transaction dates and cutomer information, group by is going to eliminate those duplicate rows.

Second ORACLE stores the date as DD-MON-YY. SO that is the date I am using to format. So my understanding is if i group by date, it will give me correct info.

Thanks once again for the nice PL/SQL Solution.......

Madhavi LOkam
consultant
Ford Motor Received on Mon May 15 1995 - 00:00:00 CEST