Home » SQL & PL/SQL » SQL & PL/SQL » List the flag change
List the flag change [message #292656] Wed, 09 January 2008 09:23 Go to next message
Messages: 21
Registered: May 2005
Junior Member
I need to have a sql to catch the flag changes based on the ID. The table data like:

ID Update_Date Flag
100 12/03/2007 Y
100 12/05/2007 Y
100 12/09/2007 N
100 12/10/2007 N
100 12/19/2007 Y
101 12/03/2007 N
101 12/09/2007 Y
101 12/21/2007 Y
102 12/07/2007 Y
102 12/18/2007 Y
102 12/24/2007 N
102 12/28/2007 N

The result should be:

ID Update_Date Flag
100 12/09/2007 N
100 12/19/2007 Y
101 12/09/2007 Y
102 12/24/2007 N

Thanks a lot for any help.

Re: List the flag change [message #292660 is a reply to message #292656] Wed, 09 January 2008 09:41 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Not tested.
But I think you can use LAG in the inner query and in the outer query you can have a filter on the lag value and the current value (when it differers print it).
You have to have inner select because window functions are not allowed in where clause.

Re: List the flag change [message #292674 is a reply to message #292656] Wed, 09 January 2008 10:24 Go to previous message
Michel Cadot
Messages: 63937
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it is easy with the LAG function.

Next time, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Also post your Oracle version (4 decimals).

In the end, post a test case: create table and insert statements then we are able to show you.


[Updated on: Wed, 09 January 2008 10:26]

Report message to a moderator

Previous Topic: Database Links
Next Topic: CASE STATEMENT help
Goto Forum:

Current Time: Thu Oct 27 13:43:39 CDT 2016

Total time taken to generate the page: 0.25142 seconds