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

Getting other fields after using MAX PostgreSQL function

I have an orders table that has a primary key id column, an order_id column, and a created_date column:

===================================
              ORDERS
===================================

id | order_id |    created_date
-----------------------------------
 1 |   178    | 2022-11-16 09:25:11
 2 |   182    | 2022-11-18 08:44:19
 3 |   178    | 2022-11-17 11:16:22
 4 |   178    | 2022-11-18 14:55:41
 5 |   195    | 2022-11-15 09:11:17
 6 |   195    | 2022-11-16 21:22:32
 7 |   146    | 2022-11-16 16:55:09
 8 |   178    | 2022-11-16 04:39:16
 9 |   121    | 2022-11-16 01:20:19

I want to write a query that returns the highest created_date for a specific order_id, so I’m trying to use MAX(). But I would also like to return the id of that highest created_date row. In the example above, let’s say that I would like to return the row that fits this criteria for order ID 178:

SELECT   MAX(o.created_date),
         o.id
FROM     orders o
WHERE    o.order_id = 178
GROUP BY o.id;

The problem is that when I write the query like this, I get multiple rows returned. I’ve tried removing the GROUP BY altogether but aside from that, I cannot wrap my head around what I would need to do to this query to show the following information:

4 | 2022-11-18 14:55:41

How can I write a PostgreSQL query to show the row with the highest created_date value but also show other information for that row?

Tags:

4

Answers


  1. 0

    You will have to use a CTE to calculate which is the latest order_id by using ROW_NUMBER() and the correct ORDER BY. After that, select the entire row using this pseudo-ranker.

    WITH ranked_order_ids_by_date AS (
  SELECT 
    *, 
    ROW_NUMBER() over (PARTITION BY order_id ORDER BY created_date DESC) AS date_rank
  FROM USERS
)
SELECT *
FROM ranked_order_ids_by_date
WHERE order_id = 178
  AND date_rank = 1

    dbfiddle

    Login or Signup to reply.
  2. 0

    If you don’t care about multiple order_id’s having the identic latest date, you can just use LIMIT:

    SELECT id, order_id, created_date
FROM orders
WHERE order_id = 178
ORDER BY created_date DESC
LIMIT 1;

    If different order id’s can appear and you want to get all of them, in Postgres DB’s, you can use FETCH FIRST 1 ROW WITH TIES (thanks to a_horse_with_no_name for that hint!):

    SELECT id, order_id, created_date
FROM orders
WHERE order_id = 178
ORDER BY created_date DESC
FETCH FIRST 1 ROW WITH TIES;

    As a more general way, you could also use a window function, for a example DENSE_RANK:

    WITH o AS
(
 SELECT orders.*, DENSE_RANK() 
  OVER (PARTITION BY order_id ORDER BY created_date DESC) AS sub
   FROM orders
  WHERE order_id = 178
)
SELECT id, order_id, created_date
  FROM o
 WHERE sub = 1
    Login or Signup to reply.
  3. 0

    An easy way to do this is to use distinct on, and get the max value by order by desc instead of max. Something like this:

    select distinct on (order_id) id, order_id, created_date
from orders
order by order_id, created_date desc
    Login or Signup to reply.
  4. 0

    Use CASE statement with sub-selects if you don’t want to use CTE:

    SELECT 
id,
order_id,
max(case when us.d_rank = 1 then us.created_date else null end) as max_created
FROM  (
            SELECT 
            created_date,
            order_id,
            dense_rank() over (partition by order_id order by created_date DESC) d_rank
            FROM users
        ) us 
 WHERE us.order_id = 178
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search