Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Self Referential Lag

919543Feb 23 2012 — edited Feb 28 2012
I was wondering if anybody has any ideas about a rather interesting query. Ideally I'd being trying to solve it using a lag function, but it needs to reference it's prior result which complicates things a little.

I have a table of sporting results, and I'd like to calculate a 'champion' column.

Take the following table of data:

Rownum - Team - Opponent - Winner
1 - A - B - A
2 - C - D - C
3 - E - F - F
4 - A - C - A
5 - A - D - D
6 - D - F - D

The Champion column should work as follows:
- Where the Previous Champion row = team, set equal to the winner, else it should be the previous row.

Which would produce the following table:

Rownum - Team - Opponent - Winner - Champion
1 - A - B - A - A
2 - C - D - C - A
3 - E - F - F - A
4 - A - C - A - A
5 - A - D - D - D
6 - D - F - D - D

Any thoughts?
This post has been answered by Frank Kulash on Feb 23 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 27 2012
Added on Feb 23 2012
7 comments
1,657 views