# [SOLVED] Add sequence number by group with constraint

## Issue

I have a `Demo` table

``````CREATE TABLE `Demo` (
`id` int(11) NOT NULL,
`seq` int(11) NOT NULL,
`old_status` int(11) NOT NULL,
`new_status` int(11) NOT NULL
)
``````

… and the demo data

``````INSERT INTO `Demo` (`id`, `seq`, `old_status`, `new_status`) VALUES
(1, 1, 1, 2),
(1, 2, 2, 3),
(1, 3, 3, 9),
(1, 4, 9, 2),
(1, 5, 2, 3),
(2, 1, 1, 2),
(2, 2, 2, 3);
``````

`Demo` table look like

id seq old_status new_status
1 1 1 2
1 2 2 3
1 3 3 9
1 4 9 2
1 5 2 3
2 1 1 2
2 2 2 3

I want to add a column that hold the version by group with condition that if we meet the `new_status` = 9 then increase the previous by one by each group of `id`.
The expected results:

id seq old_status new_status _version
1 1 1 2 1
1 2 2 3 1
1 3 3 9 2
1 4 9 2 2
1 5 2 3 2
2 1 1 2 1
2 2 2 3 1

I have tried to use `LAG` function to get my result but something went wrong!

``````SELECT id, old_status, new_status,
case
when new_status <> 9 then Lag(_version, 1) Over(PARTITION by id ORDER by seq)
else Lag(_version, 1) Over(PARTITION by id ORDER by seq) + 1
end _version
from
(select id, old_status, new_status, 1 as _version, seq
from Demo
order by id, seq) result
``````

How can I calculate `_version` directly on exists `_version` column or are there any another approaches.

## Solution

I don’t think you can do this from a computed column, but you can use `SUM()` here as an analytic function:

``````SELECT *, SUM(new_status = 9) OVER (PARTITION BY id ORDER BY seq) + 1 AS _version
FROM Demo
ORDER BY id, seq;
``````