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?
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