Firebird 3 SQL windows function over()

Question

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

Why do you expect that last row to have `someField = 200`? 100 + 200 = 300, so the result as returned by Firebird is correct given the query you use. Also, please edit your title so it summarizes your question (_"Firebird 3 SQL windows function over()"_ tells us nothing about your question, just what you're using) — Mark Rotteveel, Jul 17 '19 at 13:24

Lukasz Szozda · Answer 1 · 2019-07-16T20:43:01.033

3

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

edited Jul 16 '19 at 20:43

answered Jul 16 '19 at 20:36

Lukasz Szozda

162,964
23
234
275

Thank's your answer but not work for me. Wrong result – Tester2019 Jul 16 '19 at 20:42
@Tester2019 Please post your data. Query is correct: https://dbfiddle.uk/?rdbms=firebird_3.0&fiddle=64ef79189056a240690b97c57e7a2843 If you are using it without `WHERE` then you have to partition by customer_id – Lukasz Szozda Jul 16 '19 at 20:43
Thank you I found my mistake null value on price columnt – Tester2019 Jul 16 '19 at 21:09
@Tester2019 you are new, you can not vote yet. However upvote is not crucial, crucial is "answer acception" - that us green checkmark button UNDER upvote and downvote ones, and it should be accessible to you – Arioch 'The Jul 17 '19 at 08:17
@Tester2019 [How does accepting an answer work?](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) – Lukasz Szozda Jul 17 '19 at 16:35

Firebird 3 SQL windows function over()

1 Answers1