Getting other fields after using MAX PostgreSQL function-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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