Analytical Function at Rescue

With Analytical function in oracle, we can leverage very powerful data projections for complex data requirement.
It exhibit great tool to play around group sets with in a window and achieve desired outcome.

Will walk  through one of requirement which application team were finding difficult to implement, but with analytical function it was piece of cake.

let try to understand requirement first.
Below was the sample data set and as part of requirement for a particular City we need to have new column projecting previous Step_Number of that City within particular ID.

So expected output was similar to below.
For instance if City mentioned is Pune, for new column we would update step_number from city Pune itself.
In case City repeat itself, Step_Number need to get updated as shown below for ID = 2.

For such requirement, if we try to achieve same through PLSQL/SQL without using analytical functions surely it would add to more LOC and ultimately more overhead.

From Analytical function we would be using below.

MAX(CASE WHEN CITY = ‘Pune’
THEN STEP_NUMBER
END)
OVER (PARTITION BY ID ORDER BY STEP_NUMBER ROWS BETWEEN UNBOUNDED
PRECEDING AND 1 PRECEDING) REQ_COLUMN;

With in a window of ID, we will traverse reverse and find where city is Pune and get Max Step Number.