CODEWITHSUNDEEP
sqlsql-server
Rob
Rob

Reputation: 25

SQL to pick the next value

I have a table of values. Each value may have 1 or more entry, but only 1 should be active at any one time. The table has a primary INT ID

I need a method to make the 'current' value inactive and make the 'next' value the active value. If the current active value is the last active, instead make the first value active. Values with only 1 entry will always be active.

The sequence should work like below

Sample Sequence

Is anyone able to provide a way to achieve this?

Upvotes: 1

Views: 1179

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You should not be showing runs in separate columns. Your data should put this information in separate rows. So your data should have a separate set of rows for each run:

id    value    run    active
1     Apple     1       1
2     Apple     1       0
3     Apple     1       0
4     Banana    1       1
5     Banana    1       0
6     Cherry    1       1
1     Apple     2       0
2     Apple     2       1
3     Apple     2       0
4     Banana    2       0
5     Banana    2       1
6     Cherry    2       1

You can add the next run as:

with r as
      select t.*, max(run) over () as max_run,
             row_number() over (partition by run, value order by id) as seqnum,
             lag(active) over (partition by run, value order by id) as prev_active
      from runs
     ) 
insert into runs (id, value, run, active)
    select id, value, max_run + 1, 
           (case when prev_active = 1 then 1
                 when prev_active is null or seqnum = 1 then 1
                 else 0
            end) as active
    from r
    where run = max_run;

Upvotes: 1

Shalvin
Shalvin

Reputation: 178

Simply make a check, that is select id from the table is not max(id) of that table, then update the log to inactive and then update the id+1 to active. And if select id from the table is max(ID) then simply update that row to inactive and update min(ID) to active.

build the query, itll be fun.

Upvotes: 0

Related Questions