Count between rows

[former_SQLA_member1694869]

I want to number between the hashtags according to my new column Rownum here below. I can't solve it, anyone who knows how to do it?

My table look like this:

Tag        Pos
-------------------
#           10
(NULL)      20
(NULL)      30
(NULL)      40
#           50
(NULL)      60
(NULL)      70
#           80
(NULL)      90
#           100
(NULL)      110
(NULL)      120
(NULL)      130
(NULL)      140

I want to create one more field that counts up on each hashtag like this:

Tag        Pos      Rownr
---------------------------------
#           10      1
(NULL)      20      1
(NULL)      30      1
(NULL)      40      1
#           50      2
(NULL)      60      2
(NULL)      70      2
#           80      3
(NULL)      90      3
#           100     4
(NULL)      110     4
(NULL)      120     4
(NULL)      130     4
(NULL)      140     4

[thomas_duemesnil · Accepted solution]

You could get this with a two Window Functions.

create table Test(tag char(1), Pos integer not null primary KEY  )
insert into Test
values ('#', 10), (null, 20), (null, 30), ('#', 40), (null, 50),
(null, 60), ('#', 70), (null, 80), (null, 90), ('#', 100);

select tag, pos 
        , sum(test) over (order by pos RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "Res"
from (
    select * 
    , last_value(if tag is null then 0 else 1 endif) over (order by POS RANGE BETWEEN 1 PRECEDING AND current row) as test
    from test
) tempResult
order by pos asc

I really don't know how fast this is when you have real data.

HTH

[VolkerBarth · Accepted solution]

IMHO window functions are usually quite fast, at least way faster than self-joins that might be used to simulate them...

[thomas_duemesnil · Accepted solution]

When you have a large data set and you need to filter it you can't easily filter the raw data when you need the window function results based on the complete data set like above.

[VolkerBarth · Accepted solution]

Ah, you relate to that question How can I filter the results of a WINDOW function??

Well, as long as it is turned into a derived query, you can filter on that easily - whether the performance is great might vary but I guess it's not worse compared to other techniques where you have to compare rows of a table with each other...

[former_SQLA_member1694908]

For the results abobe you can use the if statment