SQLTeam.com | Weblogs | Forums

Combine row if ... in a querry

Hello,

I have a view which is similar to this :

ID Rank Flag
1 12 1
2 25 1
3 42 0
4 32 1
5 33 0
6 21 0
7 18 1

And here's the result of the querry I want to do

ID Rank Flag
1 12 1
2 67 1
4 86 1
7 18 1

Basicaly, I have to show rows where Flag = 1 and add the Rank(s) to the row above if Flag=0. I tried with the LAG fonction but can't find a way to manage the multiple Flag=0.

This table has more than 850e6 rows, I can't do it manualy.

To anyone who can help me, thanks.

Something like this perhaps:

with cte
  as (select id
            ,row_number() over(order by id) as rn
        from yourtable
       where flag=1
     )
select a.id
      ,sum(rank)
  from cte as a
       left outer join cte as b
                    on b.rn=a.rn+1
       inner join yourtable as c
               on c.id>=a.id
              and c.id<isnull(b.id,999999999)
 group by a.id
;
1 Like

Heh... hold the phone a minute. You said that you have to "show rows". Even if the rollup ratio were 850 to 1, that would still be a million rows. If the rollup ratio is only 10 to 1, that means 85e6 rows will be returned. What is the ultimate destination or use of these rows?

Hello,

The result will be display in a QlikSense interface, I have over 850e6 rows each month to analyse.

bitsmed, I tried your solution and it's working as expected.

Thanks