У меня есть таблица orders
, в которой есть столбец первичного ключа id
, столбец order_id
и столбец created_date
:
===================================
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
Я хочу написать запрос, который возвращает наибольшее значение created_date
для определенного order_id
, поэтому я пытаюсь использовать MAX()
. Но я также хотел бы вернуть id
этой самой высокой created_date
строки. В приведенном выше примере предположим, что я хотел бы вернуть строку, которая соответствует этому критерию для идентификатора заказа 178:
SELECT MAX(o.created_date),
o.id
FROM orders o
WHERE o.order_id = 178
GROUP BY o.id;
Проблема в том, что когда я пишу такой запрос, я получаю несколько возвращаемых строк. Я пытался полностью удалить GROUP BY
, но помимо этого я не могу понять, что мне нужно сделать с этим запросом, чтобы показать следующую информацию:
4 | 2022-11-18 14:55:41
Как я могу написать запрос PostgreSQL, чтобы показать строку с самым высоким значением created_date
, а также показать другую информацию для этой строки?
Вам нужно будет использовать CTE, чтобы вычислить, какой из них является последним order_id
, используя ROW_NUMBER()
и правильный ORDER BY
. После этого выделите всю строку с помощью этого псевдо-ранкера.
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
Если вас не волнует, что несколько идентификаторов order_id имеют одинаковую последнюю дату, вы можете просто использовать LIMIT
:
SELECT id, order_id, created_date
FROM orders
WHERE order_id = 178
ORDER BY created_date DESC
LIMIT 1;
Если могут отображаться разные идентификаторы заказов, и вы хотите получить их все, в БД Postgres вы можете использовать FETCH FIRST 1 ROW WITH TIES
(спасибо a_horse_with_no_name за подсказку!):
SELECT id, order_id, created_date
FROM orders
WHERE order_id = 178
ORDER BY created_date DESC
FETCH FIRST 1 ROW WITH TIES;
В качестве более общего способа вы также можете использовать оконную функцию, например 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
Простой способ сделать это — использовать distinct on
и получить максимальное значение с помощью order by desc
вместо max
. Что-то вроде этого:
select distinct on (order_id) id, order_id, created_date
from orders
order by order_id, created_date desc
Используйте оператор CASE
с подзапросами, если вы не хотите использовать 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
Что это за метка времени :)
2022-11-16 04:39:76