Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQL help

Re: SQL help

From: Michael McMullen <ganstadba_at_hotmail.com>
Date: Fri, 25 Aug 2006 08:39:39 -0400
Message-ID: <BAY103-DAV1246E403F0615152B014ADA6450@phx.gbl>


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
Received on Fri Aug 25 2006 - 07:39:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US