CODEWITHSUNDEEP
sqlfirebirdfirebird-3.0
Tester2019
Tester2019

Reputation: 21

Firebird 3 SQL windows function over()

I need help.. I have table orders like this

id,order_date,price,customer_id 
1 01.01.2001 100 1 
2 01.02.2001 0 1 
3 20.02.2001 0 1 
4 04.04.2001 200 1

I need select result like this

id,order_date,price,customer_id,somefield
1  01.01.2001  100   1           100
2  01.02.2001  0     1           100
3  20.02.2001  0     1           100 
3  04.04.2001  200   1           200

Try sql like this

 select a.id,order_date,
coalesce(a.price,0) price,
customer_id
sum(coalesce(a.price,0)) OVER (order by a.order_date) somefield,
     from tb_orders a
where a.customer_id=4583 and a.orderstate = 1
order by a.order_date

but result gives this

id,order_date,price,customer_id,somefield
1  01.01.2001  100   1           100
2  01.02.2001  0     1           100
3  20.02.2001  0     1           100 
3  04.04.2001  200   1           300

Upvotes: 2

Views: 1130

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175766

You could create subgroup:

SELECT *, MAX(price) OVER(PARTITION BY grp) AS somefield
FROM  (
    select a.id,order_date,
          coalesce(a.price,0) price,
          customer_id,
          sum(CASE WHEN price = 0 THEN 0 ELSE 1 END) OVER (order by a.order_date) grp
    from tb_orders a
    where a.customer_id=4583 
      and a.orderstate = 1
) sub
order by order_date;

db<>fiddle demo

Upvotes: 3

Related Questions