postgres join max date

Question

I need to construct a join that will give me the most recent price for each product. I vastly simplified the table structures for the purpose of the example, and each table row counts will be in the millions. My previous stabs at this have not exactly been very effecient.

http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 — , Apr 18 '16 at 18:27
Is the question due to your having problems writing the query to get correct results? Or that you are having problems writing the query in a way that's efficient? — Mark Hildreth, Apr 18 '16 at 18:33
@MarkHildreth I can write "functioning" queries all day long as long as the dataset is small. Once I try to run these same subquery joins on my large dbs, it never returns results. So yes, the problem is most certainly effeciency. — bopritchard, Apr 18 '16 at 18:38
@bopritchard In that case, you should provide more information. At minimum, you should give a query that you've tried to run, along with the EXPLAIN for the results. The schema and list of indexes of the tables you are trying to run on will also be useful. I'd also recommend running an analyze/vacuum on the tables and try your query again. — Mark Hildreth, Apr 18 '16 at 18:42
Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] — philipxy, Aug 13 '22 at 22:09
Possible duplicate of [Fetch the row which has the Max value for a column](https://stackoverflow.com/q/121387/3404097) — philipxy, Aug 13 '22 at 22:12

score 9 · Answer 1 · answered Apr 18 '16 at 18:28

9

In PostgreSQL, you could try DISTINCT ON to only get the first row per product id in descending create_date order;

SELECT DISTINCT ON (products.id) products.*, prices.* 
FROM products 
JOIN prices 
  ON products.id = prices.product_id 
ORDER BY products.id, create_date DESC

(of course, except for illustrative purposes, you should of course select the exact columns you need)

answered Apr 18 '16 at 18:28

Joachim Isaksson

176,943
25
281
294

This is perfect and exactly what `DISTINCT ON` is for; thank you. – Derek Tomes Dec 16 '19 at 21:29

score 3 · Answer 2 · edited Nov 08 '22 at 16:12

3

The simplest way to do it is using the row_number function.

SELECT
  p.name,
  t.amount AS latest_price
FROM (
  SELECT
    p.*,
    row_number() OVER (PARTITION BY product_id ORDER BY create_date DESC) AS rn
  FROM
    prices p) t
  JOIN products p ON p.id = t.product_id
WHERE
  rn = 1

edited Nov 08 '22 at 16:12

Sebastián Palma

32,692
6
40
59

answered Apr 18 '16 at 18:22

Vamsi Prabhala

48,685
4
36
58

score 1 · Answer 3 · edited Aug 13 '22 at 10:47

While the DISTINCT ON answer worked for my instance, I found there's a faster way for me to get what I need.

SELECT
  DISTINCT ON(u.id) u.id,
  (CAST(data AS JSON) ->> 'Finished') AS Finished,
  ee.post_value
FROM
  users_user u
  JOIN events_event ee on u.id = ee.actor_id
WHERE
  u.id > 20000
ORDER BY
  u.id DESC,
  ee.time DESC;

takes ~25s on my DB, while

SELECT
  u.id,
  (CAST(data AS JSON) ->> 'Finished') AS Finished,
  e.post_value
FROM
  users_user u
  JOIN events_event e on u.id = e.actor_id
  LEFT JOIN events_event ee on ee.actor_id = e.actor_id
  AND ee.time > e.time
WHERE
  u.id > 20000
  AND ee.id IS NULL
ORDER BY
  u.id DESC;

takes ~15s.

postgres join max date

3 Answers3