|
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 |
|
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;
I've never used dense_rank - I like it!
(12 May '20, 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 '20, 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 '20, 12:11)
Volker Barth
I solved it. Thanks for your help.
(13 May '20, 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. |
I can't understand what you intend is. Provide more details