RE: SQL help
- From: "Ken Naim" <kennaim@xxxxxxxxx>
- To: "'Michael McMullen'" <ganstadba@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 25 Aug 2006 09:50:28 -0500
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@xxxxxxxxxxx]
Sent: Friday, August 25, 2006 7:40 AM
To: kennaim@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@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
Other related posts:
