sh4rkyy
sh4rkyy

Reputation: 402

Additional condition withing partition over

https://www.db-fiddle.com/f/rgLXTu3VysD3kRwBAQK3a4/3

My problem here is that I want function partition over to start counting the rows only from certain time range.

In this example, if I would add rn = 1 at the end, order_id = 5 would be excluded from the results (because partition is ordering by paid_date and there's order_id = 6 with earlier date) but it shouldn't be as I want that time range for partition starts from '2019-01-10'.

Adding condition rn = 1expected output should be order_id 3,5,11,15, now its only 3,11,15

Upvotes: 1

Views: 307

Answers (2)

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

If priority should be given to order_id then put that before paid date in the partition function order by clause, this will solve your issue.

SELECT order_id, customer_id, amount, is_paid, paid_date,  rn FROM (
            SELECT o.*, 
                ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY  order_id,paid_date) rn
            FROM orders o
        ) x WHERE is_paid = 0 and paid_date between 
        '2019-01-10' and '2019-01-15' and rn=1

Since you need the paid date to be ordered first you need to imply a where condition in the partitioning table in order to avoid unnecessary dates interrupting the partition function.

SELECT order_id, customer_id, amount, is_paid, paid_date,  rn FROM (
        SELECT o.*, 
            ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY paid_date, order_id) rn
        FROM orders o
  where paid_date between '2019-01-10' and '2019-01-15'
    ) x WHERE is_paid = 0 and rn=1

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

use correlated subquery with not exists

DEMO

 SELECT order_id, customer_id, amount, is_paid, paid_date,  rn FROM (
        SELECT o.*, 
            ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY paid_date,order_id) rn
        FROM orders o
         WHERE paid_date between '2019-01-10' 
    and '2019-01-15'
    ) x  where rn=1 and not exists (select 1 from orders o1 where x.order_id=o1.order_id
    and is_paid=1)

OUTPUT:

order_id    customer_id amount  is_paid paid_date      rn
3           101          30      0  10/01/2019 00:00:00 1
5           102          15      0  10/01/2019 00:00:00 1
11          104          31      0  10/01/2019 00:00:00 1
15          105          11      0  10/01/2019 00:00:00 1

Upvotes: 1

Related Questions