Postgres/SQL subquery - return multiples columns per grouping based on condition

Question

Struggling with this subquery - it should be basic, but I'm missing something. I need to make these available as apart of a larger query.

I have customers, and I want to get the ONE transaction with the HIGHEST timestamp.

Customer

customer   foo
1          val1
2          val2

Transaction

tx_key  customer   timestamp   value 
1         1        11/22        10
2         1        11/23        15
3         2        11/24        20
4         2        11/25        25

The desired of the query:

customer   foo    timestamp     value   
1          val1    11/23         15
2          val2    11/25         25

I successfully wrote a subquery to calculate what I needed by using multiple sub queries, but it is very slow when I have a larger data set.

I did it like this:

(select timestamp where transaction.customer = customer.customer order by timestamp desc limit 1) as tx_timestamp 
(select value where transaction.customer = customer.customer order by timestamp desc limit 1) as tx_value

So how do I reduce this down to only calculating it once? In my real data set, i have 15 columns joined over 100k rows, so doing this over and over is not performant enough.

Gordon Linoff · Accepted Answer · 2020-11-22 21:46:10Z

2

In Postgres, the simplest method is distinct on:

select distinct on (cust_id) c.*, t.timestamp, t.value
from transactions t join
     customer c
     using (cust_id)
order by cust_id, timestamp desc;

edited Nov 22, 2020 at 21:46

answered Nov 22, 2020 at 18:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

astentx Over a year ago

Without comma after distinct on( )

Prijesh Meppayil · Accepted Answer · 2020-11-22 19:00:32Z

0

Try this query please:

SELECT 
    T.customer, T.foo, T.timestamp, T.value
FROM Transaction T 
    JOIN
(SELECT 
    customer, max(timestamp) as timestamp
from Transaction GROUP BY customer) MT ON
    T.customer = MT.customer
    AND t.timestamp = MT.timestamp

answered Nov 22, 2020 at 19:00

Prijesh Meppayil

5403 silver badges17 bronze badges

1 Comment

Liquidmetal Over a year ago

I tried this one and it worked. The only issue that I had was that one of my extra columns (e.g. another value in the transaction table, a text value though) manifested an extra row in my result when I tried.

Collectives™ on Stack Overflow

Postgres/SQL subquery - return multiples columns per grouping based on condition

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related