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's gravatar image

Rolle
548424459
accept rate: 0%

edited 11 May, 05:37

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

(12 May, 06:23) Thomas Dueme...
Comment Text Removed

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;
permanent link

answered 12 May, 10:06

Volker%20Barth's gravatar image

Volker Barth
36.1k342501749
accept rate: 34%

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.

permanent link

answered 12 May, 09:20

mrmitch's gravatar image

mrmitch
41126
accept rate: 0%

edited 12 May, 09:44

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×3
×2

question asked: 11 May, 05:36

question was seen: 123 times

last updated: 13 May, 13:14