RE: SQL help

If you have 2 or more invoices with the same date and they fall into the 3rd position, then each time you run the query oracle can choose which ever one it happens across for the third one as the sets are not ordered.

The order by should contain enough fields to guarantee the same order everytime, essentially the combination should be unique. If the situation is inherently non unqiue for whatever reason adding the primary key or rowid can serve the purpose.

Ken Naim

-----Original Message-----
From: Michael McMullen [mailto:ganstadba_at_hotmail.com] Sent: Friday, August 25, 2006 7:40 AM
To: kennaim_at_gmail.com; oracle-l_at_freelists.org Subject: Re: SQL help

I'm a little confused by "than 3 invoice per day you need to add something to the order by so you get the same 3 records each time you run it" I've always assumed, the query would materialize. So if you had a 100 invoices, they would be ordered by invoice_date desc then the query would work it's way up the chain and rn = 3 would give you the third last invoice. This post and something I read the other day on asktom would suggest that I could be wrong.

Mike
----- Original Message -----
From: Ken Naim
To: Roger.Xu_at_dp7upbg.com ; 'Oracle-L_at_Freelists. Org (E-mail)' Sent: Thursday, August 24, 2006 4:04 PM
Subject: RE: SQL help

Select * from (Select customer_number, invoicedate, row_number() over (partition by customer_number, order by invoice_date desc) rn from order_history) where rn =3. if you have more than 3 invoice per day you need

to add something to the order by so you get the same 3 records each time you

run it, or you need to replace row_number with rank or dense_rank depending on your exact needs.

Ken Naim

--
http://www.freelists.org/webpage/oracle-l