Question 264578 in PostgreSQL Question posted in
The official documentation can be found here.

Getting item with most recent date in PostGreSQL

Suppose I have queried a table called items_run to get the following results:

object_id  item_id  run_date
    0       1010    8/1/2023
    1       1020    8/3/2023
    2       1030    8/4/2023
    3       1010    8/5/2023
    4       1020    8/6/2023

Here’s the query:

SELECT object_id,  
       item_id,
       run_date
FROM items_run
WHERE run_date > '2023-07-30'

I want to adjust my query to select the rows with only the most recent run_date for a particular item_id. So it would look like this:

    object_id  item_id  run_date
       0        1010    8/5/2023
       1        1020    8/6/2023
       2        1030    8/4/2023

I have tried to add WHERE run_date > '2023-07-30' AND run_date = (SELECT MAX(run_date) FROM items_run), but that just returned an empty table. How could I accomplish my goal here?

Tags:

2

Answers


  1. 0

    As one option, you can get the max run_date for each item_id into a result set:

    SELECT item_id, max(run_date) as run_date 
FROM items_run 
WHERE run_date > '2023-07-30'

    Then you can use that as a subquery in a join:

    SELECT MIN(object_id) object_id,
    ir.item_id,
    irmax.run_date
FROM items_run ir
    INNER JOIN 
        (
            SELECT item_id, max(run_date) as run_date 
            FROM items_run 
            WHERE run_date > '2023-07-30'
        ) irmax
    ON ir.item_id = irmax.item_id
WHERE run_date > '2023-07-30'
GROUP BY ir.item_id, irmax.run_date
    Login or Signup to reply.
  2. 0

    A simple aggregate gives you exactly what you’re asking for:

    SELECT
    MIN(object_id)
    , item_id
    , MAX(run_date)
FROM items_run
WHERE run_date > '2023-07-30'
GROUP BY item_id
ORDER BY 1; -- first column
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search