Re: Analytic function Help

From: Andreas Mosmann <mosmann_at_expires-28-02-2011.news-group.org>
Date: Fri, 11 Feb 2011 10:21:57 +0100
Message-ID: <1297416117.13_at_user.newsoffice.de>

Lennart Jonsson schrieb am 01.02.2011 in <ii9rah$538$1_at_news.eternal-september.org>:

Sorry, I was involved in other work.

> select id, start, stop, cnt,
> row_number() over (partition by id order by start) -
> row_number() over (partition by id, cnt order by start) as diff
> from T
> order by start

> ID START STOP CNT DIFF
> -- ----------- ----------- ----------- --------------------
> 01 0 100 2 0
> 01 100 150 2 0
> 01 150 375 4 2
> 01 375 500 1 3
> 01 500 700 1 3
> 01 700 900 2 3
> 01 900 1000 2 3

> min(start) and max(stop) per id, diff gives:

> select id, min(start) as start, max(stop) as stop, cnt, diff
> from (
> select id, start, stop, cnt,
> row_number() over (partition by id order by start) -
> row_number() over (partition by id, cnt
> order by start) as diff
> from T
> )
> group by id, cnt, diff
> order by start

> ID START STOP CNT DIFF
> -- ----------- ----------- ----------- --------------------
> 01 0 150 2 0
> 01 150 375 4 2
> 01 375 700 1 3
> 01 700 1000 2 3

> I'll have to stop here, if I come up with some other way I might post
> that later.

Thank you very much, that was the idea I looked for.

The difference between the row number without criteria and the row number within this criteria devides into groups, as I want and after this min/max will work. Fine Idea

> /Lennart

Andreas

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de

Received on Fri Feb 11 2011 - 03:21:57 CST