CODEWITHSUNDEEP
sqlpostgresql
logan_b
logan_b

Reputation: 43

List all payments that were, at the time of the payment, the largest payment to date

I have a table of payments and what I'm looking to do is get all payments that were, at the time of the payment, the largest payment to date.

I have the following query:

SELECT MAX(t.amount), t.date
FROM (
    SELECT MAX(pay.amount) AS amount, DATE(pay.payment_date)
    FROM payment pay
    JOIN payment pay2 ON pay2.payment_id = pay.payment_id
    WHERE pay2.payment_date <= pay.payment_date
    GROUP BY DATE(pay.payment_date), pay.amount
    ORDER BY DATE(pay.payment_date)
) t
GROUP BY t.date;

Which returns the following output:

| amount | date       |
|--------|------------|
| 10.99  | 2007-02-21 |
| 10.99  | 2007-03-01 |
| 11.99  | 2007-03-02 |
| 10.99  | 2007-03-16 |
| 10.99  | 2007-03-17 |

What it should be outputting is 11.99 for everything after 2007-03-02

I'm not sure where the snare in my logic is so any help is greatly appreciated. Even moreso if you can help me figure out how to get all of the payment details and not just the date and amount.

Upvotes: 1

Views: 63

Answers (1)

Hambone
Hambone

Reputation: 16377

This seemed to work on some test data, but try it and see. It uses the max analytic function:

with cte1 as (
    SELECT MAX(pay.amount) AS amount, DATE(pay.payment_date)
    FROM payment pay
    JOIN payment pay2 ON pay2.payment_id = pay.payment_id
    WHERE pay2.payment_date <= pay.payment_date
    GROUP BY DATE(pay.payment_date), pay.amount
),
cte2 as (
  SELECT MAX(t.amount) as amount, t.date
  from cte1
  GROUP BY t.date
)
select
  amount, date,
  max (amount) over (order by date)
from cte2

Upvotes: 1

Related Questions