SQL create a group column?

[thomas_duemesnil]

I have a field with Pos in a table. Now I want to create a group value that I will use later in a new column with sql like this: I've tested functions for last_value and first_value, but I couldn't solve it.

Pos GroupValue
--- ----------
30      1
30      1
100     0
100     0
100     0
110     1
110     1
110     1
120     0
120     0
130     1
130     1
130     1
130     1
140     0
140     0
150     1
150     1

[thomas_duemesnil]

I can't understand what you intend is. Provide more details

[Former Member]

Your group value could be a case when MOD((select count(distinct(y.pos)) from yourtable y where y.pos<pos)/2) = 0 then 1 else 0 end

the count on 30 is 0 so 1 the count on 100 is 1 so 0

Basically an even/odd position gives you the group, so you could also do a cte with row number and distinct pos order by pos and modulus that on != 0.