How to select row with max date?

Question

PostgreSQL

There is the following table:

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

You need to get information about recent actions. who and when was last created, modified and signed. ie the output should be:
1 | Ivanov | created | datetime1
6 | Sidorov | changed | datetime6
7 | Sidorov | Signed | datetime7

That is, you need to select the max date (the row with the max date) and the rest of the fields of this row.

Naturally, such a query does not work (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

If you add the usr field to the group by field, then an extra grouping appears.

Here's what I came up with, it works correctly, but the option is still not working:

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

Answer 1

PostgreSQL has a nice handy distinct on construct :

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

Answer 2

Try to group by usr and action, and pull up the maximum id and date to them.
In general, a crutch may not work in the best way. If such a selection is needed often, but it changes less than 10 minutes, for example, then you can make a materialized view, which will allow you to spend time rebuilding it, and the selection will be fast enough.
PS Here's what I was talking about:

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

Answer 3

Yes, there is no good solution here.
The last solution can only be secured.

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

I didn’t check for execution, but the essence was passed to
PS by the MSSQL syntax, but I hope there is a JOIN in postgres =)

Answer 4

In postgres, if my memory serves me, there are window functions. That. the request could be like this:

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;

Oracle allows this:
id usr action datetime rn
----------------------------------------- ----------------------------
1 Ivanov created on 2016.12.08 14:14:30 1
6 Sidorov changed on 2016.12.08 14:16: 37 1
7 Sidorov signed 2016.12.08 14:17:37 1