CODEWITHSUNDEEP
postgresql
eponkratova
eponkratova

Reputation: 477

Extract amount for the minimum date in Postgres

Very basic: I have a table with dates, account and amount done by a particular account on that date. I am stuck on a very basic problem - get the amount for the minimum date per account.

Input:

enter image description here

Desired:

enter image description here

If I do the query below it obviously returns the grouping by the amount, too.

SELECT account_ref         AS account_alias, 
       Min(timestamp_made) AS reg_date, 
       amount 
FROM   stg_payment_mysql 
GROUP  BY account_ref, 
          amount

Upvotes: 0

Views: 420

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

Perhaps the most performant way to do this would be to use DISTINCT ON:

SELECT DISTINCT ON (account) account, date, amount
FROM stg_payment_mysql
ORDER BY account, date;

A more general ANSI SQL approach to this would use ROW_NUMBER:

WITH cte AS (
    SELECT account, date, amount,
        ROW_NUMBER() OVER (PARTITION BY account ORDER BY date) rn
    FROM stg_payment_mysql
)

SELECT account, date, amount
FROM cte
WHERE rn = 1
ORDER BY account;

Upvotes: 1

Related Questions