Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

how to get most recent consecutive records with the same value

725150Nov 22 2010 — edited Dec 3 2010
Hi,

Can someone please help me to solve my problem?

Below is my table

Prod_Code-----Purchase_date---Inv_number-------Inv_amt
MS_AV16850------18/01/2005-----------6575947----------------7.93
MS_AV16850------22/07/2005-----------7034012----------------51.82
MS_AV16850------04/01/2006-----------8719618----------------51.82
MS_AV16850------20/06/2006-----------9515864----------------104.69
MS_AV16850------16/04/2007-----------10353759----------------189.29
MS_AV16850------30/05/2007-----------10689899----------------189.29
MS_AV16850------06/01/2008-----------1653821----------------65.49
MS_AV16850------22/02/2009-----------10866311----------------189.29

I want my query to show the rows that has most recent purchase dates with same amount in consecutive purchase date.

So from the table above, the query should display:

Prod_Code-----Purchase_date---Inv_number-------Inv_amt
MS_AV16850------16/04/2007-----------10353759----------------189.29
MS_AV16850------30/05/2007-----------10689899----------------189.29


It should not get

MS_AV16850------16/04/2007-----------10353759----------------189.29
MS_AV16850------30/05/2007-----------10689899----------------189.29
MS_AV16850------22/02/2009-----------10866311----------------189.29

because inv_number 10866311 has a prvevious inv_amount of 65.49.

and not get this

MS_AV16850------22/07/2005-----------7034012----------------51.82
MS_AV16850------04/01/2006-----------8719618----------------51.82

because they are not the most recent purchase date even if they have the same inv_amount.


Thanks in advance.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 31 2010
Added on Nov 22 2010
13 comments
7,952 views