Search code examples
sqlsql-servert-sqlsql-order-bywindow-functions

how can i alternate between 0 and 1 values in sql server?

I want to create a select which will alternate between 1 and 0

my table looks like that

id1    id2    al
11     1      1
40     1      0
12     1      0
237    1      1

but I want to make it like that

id1    id2    al
40     1      0
11     1      1
12     1      0
237    1      1

I want to keep the same values in my table but I just want to switch the rows to alternate between 0 and 1

Solution

  • Consider:

    select *
from mytable
order by row_number() over(partition by al order by id1), al

    This alternates 0 and 1 values - if the groups have a different number of rows, then, once the smallest group exhausts, all remaining rows in the other group appear at the end of the resultset.

    I am unsure which column you want to use to order the rows within each group - I assumed id1, but you might want to change that to your actual requirement.