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@xxxxxxxxxxx ; 'Oracle-L@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
-- //www.freelists.org/webpage/oracle-l
