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

[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 Member · Accepted solution]

I've never used dense_rank - I like it!

[Former Member · 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 Member · Accepted solution]

I solved it. Thanks for your help.