Re: Analytic function Help

"Andreas Mosmann" <mosmann_at_expires-31-01-2011.news-group.org> a écrit dans le message de news: 1296500556.65_at_user.newsoffice.de...
| Hi, there is a view like
|
|
| ID SORT FK
| 01 -5 N
| 01 -4 N
| 01 -3 Y
| 01 -2 Y
| 01 -1 N
| 01 0 Y
| 01 1 N
| 01 2 N
| 02 -2 N
| 02 -1 Y
| 02 0 Y
| 02 1 Y
| 03 -5 Y
| 03 -4 N
| 03 -3 Y
| 03 -2 N
| 03 -1 Y
| 03 0 N
| 03 1 Y
|
| I need the number of seperate groups of 'Y'
| that means in this case:
|
| ID COUNT
| 01 2
| 02 1
| 03 4
|
| ID 01: SORT (-2,-3) and (0) are a group ->2
| ID 02: SORT (-1 .. +1) is a group ->1
| ID 03: SORT (-5), (-3), (-1), (1) are groups ->4
|
| I sometimes use lead and lag, but in this case I do not have an idea,
| but I feel it should be possible, isn't it?
|
| Thank you
| Andreas
|
| --
| wenn email, dann AndreasMosmann <bei> web <punkt> de

Something like
count(case when fk='Y' and nvl(lag(fk) over (partition by id order by sort),'N')='N' then 1 end) with group by id

Regards
Michel Received on Mon Jan 31 2011 - 13:26:53 CST