SQL create a group column?

[former_SQLA_member1694869]

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

[VolkerBarth · Accepted solution]

A nice use case for OLAP, methinks:

create table TestPos( 
    -- add a PK column just to show how to conserve original order
   PK  int default autoincrement primary key,
   Pos int not null
);

-- Sample date
insert TestPos(Pos)
values
(30), (30),
(100), (100), (100),
(110), (110), (110),
(120), (120),
(130), (130), (130), (130),
(140), (140),
(150), (150);

-- Use the DENSE_RANK window function to "number" the groups with identical values
-- and apply MOD 2 to the result
select Pos, mod(PosRank, 2) as GroupValue
from
   (select PK, Pos, dense_rank() over (order by Pos) as PosRank
   from TestPos
   order by 1) DT
order by PK, Pos;

-- You can also directly apply MOD to the window function
select Pos, mod(dense_rank() over (order by Pos), 2) as GroupValue
from TestPos
order by Pos;

[former_SQLA_member1694949 · Accepted solution]

I've never used dense_rank - I like it!

[former_SQLA_member1694869 · Accepted solution]

Excellent solution. Exactly what I needed. A small problem. If Pos doesn't come in sorted order, can it be solved the same way then? If it looks like this:

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

[VolkerBarth · Accepted solution]

Hm, I don't fully understand your question, and do not know how to specify a sort order that sorts 150 between 30 and 100 - but well, you have to apply your required sort order both in the DENSE_RANKE OVER (ORDER BY ...) and the final ORDER BY.

[former_SQLA_member1694869 · Accepted solution]

I solved it. Thanks for your help.

[former_SQLA_member1694949]

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.