How to select the row with the maximum date?

Have the following table:
id | usr | action | datetime
____________________________________
1 | Ivanov | created | datetime1
2 | Ivanov | changed | datetime2
3 | Petrov | changed | datetime3
4 | DOE | changed | datetime4
5 | Petrov | changed | datetime5
6 | Sidorov | changed | datetime6
7 | Sidorov | Signed | datetime7


Need to get information recent activity. who and when was the last time created, changed and signed. t e the output should be:
1 | Ivanov | created | datetime1
6 | Sidorov | changed | datetime6
7 | Sidorov | Signed | datetime7

Te need to select the max date(row with max date) and the remaining fields of the given string

This query of course 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 a group by field usr, then there is an extra group.

That's what I got working right, 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
)
July 2nd 19 at 13:29
4 answers
July 2nd 19 at 13:31
Solution
In PostgreSQL there is a wonderful user-friendly design distinct on:
select distinct on (1) -- select only unique values on the first field (action) from the sample
action, - to bring action, usr, datetime
usr
datetime
from table t1
1 order by datetime desc -- for sample sort on the first field (action), then by date in reverse order
brilliant))) thank you - August20 commented on July 2nd 19 at 13:34
I wonder how will behave such a request, when will the 2 same date? - nannie72 commented on July 2nd 19 at 13:37
: Take the first popavsheysya. If you want to control it, can add another condition in the ORDER BY after the date - Markus_Langworth commented on July 2nd 19 at 13:40
: Ie PSQL I can in ORDER to use the fields which are not in the query? - nannie72 commented on July 2nd 19 at 13:43
Of course! - Markus_Langworth commented on July 2nd 19 at 13:46
The main thing is that these fields were in the tables for which you made a selection - Markus_Langworth commented on July 2nd 19 at 13:49
:
select distinct on (1)
action,
usr
datetime
from table t1
order by 1, datetime desc, id desc -- will work?

I did not know, thanks for the enlightenment - nannie72 commented on July 2nd 19 at 13:52
Will work. Is PostgreSQL made by people for people. - Markus_Langworth commented on July 2nd 19 at 13:55
Postgres Postgres, and you can write more universal:
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

Important points: inner join and the fact that we add no table and the sample. I am ashamed that I did not think before. little experience - August20 commented on July 2nd 19 at 13:58
July 2nd 19 at 13:33
Try to group by usr and action, and for them to pull the maximum id and date.
In General, the crutch may not work in the best way. If such a sample is desired but often it changes at least 10 minutes, for example, you can make a materialized view that will allow you to spend time on its restructuring, and the sample will be fast enough.

P. S. That's about it, I said:
select 
 max(id) id
x.usr
x.action
max(datetime) datetime
from test x
group by x.usr x.action
order by id
about what I will do. and the extra entries will screen in php. thank you - August20 commented on July 2nd 19 at 13:36
July 2nd 19 at 13:35
Yes, there is a good solution.
The last solution can only be safe.

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


Have not tested for execution, but the point is conveyed

PS setaxis MSSQL, but I hope there is Postgres JOIN =)
July 2nd 19 at 13:37
In postgres, if I remember correctly, there are window functions. Thus, the query may be:

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 action datetime usr rn
---------------------------------------------------------------------
1 Ivanov created 2016.12.08 14:14:30 1
6 Sidorov changed 2016.12.08 14:16:37 1
7 Sidorov signed 2016.12.08 14:17:37 1
Postgres Postgres, and you can write more universal:
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

Important points: inner join and the fact that we add no table and the sample. I am ashamed that I did not think before. little experience.

it is a fully universal solution like. - August20 commented on July 2nd 19 at 13:40
: well... I on your place would be ashamed of the omission of the need for a universal solution. So people see the tag "POSTGRESQL" - and try to suggest exactly this. For MySQL, for example, I would even the phrase "window function" is not remembered=) - nannie72 commented on July 2nd 19 at 13:43
: no, universalist not mandatory, and I left the option from the comments above. thank you for the window function - Markus_Langworth commented on July 2nd 19 at 13:46

Find more questions by tags SQLPostgreSQL