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: 2108
Registered: October 2003
Location: Hyderabad, India
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: 65144
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: how to insert data from one table to another table
Next Topic: how to reject trailing spaces?
Goto Forum:

Current Time: Sun Aug 20 05:00:19 CDT 2017

Total time taken to generate the page: 0.06061 seconds