Как выбрать строку с максимальной датой?

Question

[Александр Афанасьев]

Есть следующая таблица:

id | usr | action | datetime
____________________________________
1 | Иванов |  создал  |   datetime1
2 | Иванов  |  изменил |   datetime2
3 | Петров |  изменил |   datetime3
4 | Петров  |  изменил |   datetime4
5 | Петров  |  изменил |   datetime5
6 | Сидоров |  изменил |   datetime6
7 | Сидоров |  Подписал |   datetime7

Необходимо получить информацию последних действиях. кто и когда последний раз создал, изменил и подписал. т е на выходе должно быть:
1 | Иванов | создал | datetime1
6 | Сидоров | изменил | datetime6
7 | Сидоров | Подписал | datetime7

Т е нужно выбрать макс дату(строку с макс датой) и остальные поля данной строки

Вот такой запрос естественно не работает(column must appear in the GROUP BY clause or be used in an aggregate function):

select max(datetime), usr, action 
from table
group by action

Если добавить в group by поле usr, то появляется лишняя группировка.

Вот до чего я дошел, работает верно, но вариант все равно не рабочий:

select * 
from table t1
where t1.date in (
select max(t2.date)
from table t2
group by t2.actioncode
)

Answer 1

[Александр Шелеметьев]

В PostgreSQL есть замечательная удобная конструкция distinct on:

select distinct on (1) -- выбрать только уникальные значения по первому полю (action) из выборки
action, -- вывести action, usr, datetime
usr,
datetime
from table t1
order by 1, datetime desc -- для выборки отсортировать по первому полю (action), затем по дате в обратном порядке

Comments

[Александр Афанасьев]

гениально))) спасибо

[Alex]

Интересно, как будет себя вести такой запрос, когда будет 2 одинаковые даты?

[Александр Шелеметьев]

Alex: Возьмёт первую попавшуся. Если желаете этим управлять, можете добавить ещё одно условие в ORDER BY после даты

[Alex]

Александр Шелеметьев: Т.е. PSQL я могу в ORDER использовать поля которых нет в запросе?

[Александр Шелеметьев]

Конечно!

[Александр Шелеметьев]

Александр Шелеметьев: Главное, чтобы эти поля были в таблицах, по которым вы делаете выборку

[Alex]

Александр Шелеметьев:
select distinct on (1)
action,
usr,
datetime
from table t1
order by 1, datetime desc, id desc -- будет работать?

Не знал, спасибо за просвещение

[Александр Шелеметьев]

Будет работать. Это PostgreSQL - сделано людьми для людей.

[Александр Афанасьев]

Постгрес постгресом, а написать можно универсальнее:
select t.id, t.usr, t.action, d.datetime
from table as t
inner join ( select id, usr, action, max(datetime) from table group by action) as d on t.action=d.action and t.action=d.action and t.datetime=d.datetime

Важные моменты: inner join и то что мы присоединяем не таблицу а выборку. мне стыдно что я не догадался до этого. мало опыта

Answer 2

[GameHasNoName]

В postgres, если мне не изменяет память, есть оконные функции. Т.о. запрос может быть таким:

select *
    from (select id,
                 usr,
                 action,
                 datetime,
                 row_number () over (partition by action order by datetime desc) rn
            from test_tbl)
   where rn = 1
order by datetime;

Оракл такое позволяет:

id usr action datetime rn
---------------------------------------------------------------------
1 Иванов создал 2016.12.08 14:14:30 1
6 Сидоров изменил 2016.12.08 14:16:37 1
7 Сидоров подписал 2016.12.08 14:17:37 1

Comments

[Александр Афанасьев]

Постгрес постгресом, а написать можно универсальнее:
select t.id, t.usr, t.action, d.datetime
from table as t
inner join ( select id, usr, action, max(datetime) from table group by action) as d on t.action=d.action and t.action=d.action and t.datetime=d.datetime

Важные моменты: inner join и то что мы присоединяем не таблицу а выборку. мне стыдно что я не догадался до этого. мало опыта.

это полностью универсальное решение вроде.

[GameHasNoName]

Александр Афанасьев: ну... мне на вашем месте было бы стыдно за неуказание необходимости универсального решения. Так-то люди видят тэг "POSTGRESQL" - и стараются подсказать конкретно в этом ключе. Для MySQL, например, я бы даже фразу "оконные функции" не вспомнил=)

[Александр Афанасьев]

GameHasNoName: нет, универсальнасть не обязательна, и я оставил вариант из коммента выше. вам спасибо за оконные функции

Answer 3

[Александр Кузнецов]

Попробуйте группировать по usr и action, а к ним подтянуть максимальные id и дату.
Вообще костыль, может работать не самым лучшим образом. Если такая выборка нужна часто но она меняется реже 10 минут, к примеру, то можно сделать материализованное представление, что позволит тратить время на его перестройку, а выборка будет достаточно быстрой.

P.S. Вот про него я говорил:

select 
  max(id) id
, x.usr
, x.action
, max(datetime) datetime
from test x
group by x.usr, x.action
order by id

Comments

[Александр Афанасьев]

примерно так я и сделаю. а лишние записи отсею на php. спасибо

Answer 4

[Alex]

Да, тут хорошего решения быть не может.
Последнее решение можно только обезопасить.

SELECT *
FROM TABLE t5
JOIN (
	SELECT MAX(t1.id) AS id
		,t1.action
		,t1.DATE
	FROM TABLE t1
	JOIN (
		SELECT t2.action
			,max(t2.DATE) AS DATE
		FROM TABLE t2
		GROUP BY t2.action
		) t3 ON t1.action = t3.action
		AND t1.DATE = t3.DATE
	GROUP BY t1.action
		,t1.DATE
	) t4 ON t4.id = t5.id

Не проверял на выполнение, но суть передал

P.S. ситаксис MSSQL, но надеюсь, в постгресе есть JOIN =)