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