CODEWITHSUNDEEP
mysqlsqlmariadb
Daniel Aviv
Daniel Aviv

Reputation: 159

Filtering out rows based on other rows

I have a table that contains records of pending and completed transactions. each record in the table is representing a step in the lifecycle of a transaction:

e.x.: a successful transaction would have 2 records in the table with the same timestamp:

- id: 1, timestamp: 111111111, type: pending, created_at: 2020-10-10
- id: 2, timestamp: 111111111, type: complete, created_at: 2020-14-10

I want to select all transactions and filter them out in this way:

All completed transactions should be displayed, Any pending with a matching complete timestamp should be filtered out, Any pending without a matching complete timestamp that is greater than 7 days old should be filtered out as well.

the wanted output should be like this:

all rows in the table:

- id: 1, timestamp: 111111111, type: pending, created_at: 2020-10-10
- id: 2, timestamp: 111111111, type: complete, created_at: 2020-14-10
- id: 3, timestamp: 222222222, type: pending, created_at: 1999-01-01

output:

- id: 2, timestamp: 111111111, type: complete, created_at: 2020-14-10

Upvotes: 1

Views: 190

Answers (2)

Popeye
Popeye

Reputation: 35900

You can use window function as follows:

Select * from
(Select t.*,
       Max(case when type = 'complete' created_at end) over (partition by timestamp) as mx_c
  From t) t
Where type = 'completed' or (type = 'pending' and (created_at <= mx_c - interval 7 day or mx_c is null)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Hmmm . . . if I understand correctly, you can convert these to where conditions:

select t.*
from transactions t
where t.type = 'complete' or
      (t.type = 'pending' and 
       not exists (select 1
                   from transactions t2
                   where t2.timestamp = t.timestamp and
                         t2.type = 'complete'
                  ) and
       t.timestamp >= now() - interval 7 day
      );

Upvotes: 1

Related Questions