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

asked 12 Feb '20, 07:54

Rolle's gravatar image

Rolle
558495161
accept rate: 0%

edited 13 Feb '20, 06:33

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.7k293965


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

permanent link

answered 13 Feb '20, 06:31

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.7k293965
accept rate: 17%

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

(13 Feb '20, 06:58) Volker Barth
Replies hidden

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.

(13 Feb '20, 07:03) Thomas Dueme...

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...

(13 Feb '20, 07:22) Volker Barth

For the results abobe you can use the if statment

permanent link

answered 13 Feb '20, 02:54

SamuelCosta's gravatar image

SamuelCosta
71338
accept rate: 20%

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:

×19
×8

question asked: 12 Feb '20, 07:54

question was seen: 933 times

last updated: 13 Feb '20, 07:23