Получение других полей после использования функции MAX PostgreSQL

У меня есть таблица 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, а также показать другую информацию для этой строки?

Что это за метка времени :) 2022-11-16 04:39:76

VvdL 21.11.2022 11:54
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
1
75
4
Перейти к ответу Данный вопрос помечен как решенный

Ответы 4

Вам нужно будет использовать 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

Другие вопросы по теме