| Group | Rank | Category |
|---|---|---|
| A | 1 | New business |
| A | 2 | Adjustment |
| A | 3 | Adjustment |
| A | 4 | Renewal |
| A | 5 | Adjustment |
| A | 6 | Cancellation |
| B | 1 | New Business |
| B | 2 | Renewal |
| B | 3 | Adjustment |
| B | 4 | Cancellation |
| C | 1 | Adjustment |
The goal is to replace all the 'Adjustment' with the above value group wise. The category values for each group could start with any value except Cancellation. If it starts with Adjustment itself, then it should be left untouched.
Output:
| Group | Rank | Category |
|---|---|---|
| A | 1 | New business |
| A | 2 | New business |
| A | 3 | New business |
| A | 4 | Renewal |
| A | 5 | Renewal |
| A | 6 | Cancellation |
| B | 1 | New Business |
| B | 2 | Renewal |
| B | 3 | Renewal |
| B | 4 | Cancellation |
| C | 1 | Adjustment |
CodePudding user response：
It is possible. The idea is to use 
CodePudding user response：
You could use a lag statement enclosed in an iff.
select group,
rank,
iff(category = 'Adjustment',
lag(category, 1, category) over (partition by group order by group, rank),
category) as category
from table
CodePudding user response：
This does not answer the question exactly however I thought it provides a different approach to the solution.
Instead of the lag() approach (which is the perfect solution for this problem) you could choose to ignore the 'Adjustments' altogether with the exception of the initial record.
The result is similar it just removes the repeated rows (which don't really add any value do they?).
Keeping the Rank shows the number of rows removed ... and at scale would reduce the size of the dataset without removing any information -> which I think is the goal of any data practitioner.
Any thoughts welcomed :-)
with cte as (
SELECT 'A' AS grp, 1 AS rnk, 'New business' AS category FROM DUAL
UNION ALL SELECT 'A' AS grp, 2 AS rnk, 'Adjustment' FROM DUAL
UNION ALL SELECT 'A' AS grp, 3 AS rnk, 'Adjustment' FROM DUAL
UNION ALL SELECT 'A' AS grp, 4 AS rnk, 'Renewal' FROM DUAL
UNION ALL SELECT 'A' AS grp, 5 AS rnk, 'Adjustment' FROM DUAL
UNION ALL SELECT 'A' AS grp, 6 AS rnk, 'Cancellation' FROM DUAL
UNION ALL SELECT 'B' AS grp, 1 AS rnk, 'New Business' FROM DUAL
UNION ALL SELECT 'B' AS grp, 2 AS rnk, 'Renewal' FROM DUAL
UNION ALL SELECT 'B' AS grp, 3 AS rnk, 'Adjustment' FROM DUAL
UNION ALL SELECT 'B' AS grp, 4 AS rnk, 'Cancellation' FROM DUAL
UNION ALL SELECT 'C' AS grp, 1 AS rnk, 'Adjustment' FROM DUAL )
select
cte.*
from
cte
left outer join
(select grp,rnk,category from cte where iff(rnk=1,'z',category)!='Adjustment') yay on yay.grp cte.grp and yay.rnk=cte.rnk
where yay.grp is not null;