Indicate a row that cause an abnormal case (SQL)-CodePudding

I have a result as below using the following script:

SELECT 
    id, (2022 - age) yearId, age, [value],
    CASE 
        WHEN LAG([value], 1, 0) OVER (PARTITION BY id ORDER BY [age]) = 0 
            THEN 'Base' 
        WHEN [value] > LAG([value], 1, -1) OVER (PARTITION BY id ORDER BY [age]) 
            THEN 'Increasing'
        WHEN [value] = LAG([value], 1, -1) OVER (PARTITION BY id ORDER BY [age]) 
            THEN 'No Change'
        ELSE 'Decreasing'
    END AS [Order]
FROM Test

Values

And I manage to get a group of ids with an id causing a "flip: decreasing and then increasing or the other way around" as:

Abnormal Case

Now I want to print out the same result as above but with a column indicates the row that cause the flip, something like this (the row causes the flip should be place at the top of each partition):

Id	age	value	flip
1	4	3	1
1	0	5	0
1	1	4	0
1	2	3	0
1	3	2	0
1	5	3	0
1	6	4	0

Thank you!

CodePudding user response：

Expanding your existing logic to get the previous order value then conditionally ordering

with cte as
(
SELECT 
    id, (2022 - age) yearId, age, [value],
    CASE 
        WHEN LAG([value], 1, 0) OVER (PARTITION BY id ORDER BY [age]) = 0 
            THEN 'Base' 
        WHEN [value] > LAG([value], 1, -1) OVER (PARTITION BY id ORDER BY [age]) 
            THEN 'Increasing'
        WHEN [value] = LAG([value], 1, -1) OVER (PARTITION BY id ORDER BY [age]) 
            THEN 'No Change'
        ELSE 'Decreasing'
    END AS [Order]
FROM T1
) ,
cte1 as
(select cte.*,concat(cte.[order], lag([order]) over (partition by id order by age)) concatlag
from cte)

select * ,
    case when concatlag in('IncreasingDecreasing','DecreasingIncreasing') then 1 else 0 end
from cte1
order by 
    case when concatlag in('IncreasingDecreasing','DecreasingIncreasing') then 1 else 0 end desc,
    age