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 asked 11 May, 05:36 Rolle 
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; answered 12 May, 10:06 Volker Barth I've never used dense_rank  I like it!
(12 May, 10:22)
mrmitch
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
(12 May, 11:35)
Rolle
Replies hidden
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.
(12 May, 12:11)
Volker Barth
I solved it. Thanks for your help.
(13 May, 13:14)
Rolle

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. answered 12 May, 09:20 mrmitch 
I can't understand what you intend is. Provide more details