[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;

Answered By – Tim Biegeleisen

Answer Checked By – Senaida (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.