Self Referential Lag
919543Feb 23 2012 — edited Feb 28 2012I 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?