I have a DB where i store several info about actions performed based on a date, so for example i have the following rows
"John", "Action1, "2017-02-20"
"John", "Action2, "2017-02-10"
"Mark", "Action3", "2016-09-21"
"Mark", "Action4", "2016-03-11"
If i would like to return the most recent activity for each user (John Action1, Mark Action3), can i do with a SQL sentence, or do i need to filter that in my source code once the SQL sentence has returned all the values?
Its a Postgres DB
Thanks
The typical way of doing this uses row_number():
select t.*
from (select t.*,
row_number() over (partition by col1 order by col3 desc) as seqnum
from t
) t
where seqnum = 1;
row_number() is an ANSI standard function available in almost all databases.
This should work.
---sample data
WITH mytable([name], [action], [date]) AS
(SELECT 'John',
'Action1',
'2017-02-20'
UNION ALL SELECT 'John',
'Action2',
'2017-02-10'
UNION ALL SELECT 'Mark',
'Action3',
'2016-09-21'
UNION ALL SELECT 'Mark',
'Action4',
'2016-03-11')
---actual query
SELECT t.[name],
mt.[action]
FROM
(SELECT [name],
MAX([date]) AS [date]
FROM [mytable]
GROUP BY [name]) t
INNER JOIN [mytable] mt ON t.[name] = mt.[name]
AND t.[date] = mt.[date]
I've just run the following on a postgres instance - hopefully it's in good ANSI SQL and should work in other db's:
SELECT ac.name, ac.action, ac.time
FROM action_times ac
JOIN (SELECT name, MAX(time) AS time
FROM action_times GROUP BY name) mx
USING (name, time);
Which gives:
name | action | time
------+---------+------------
Mark | Action3 | 2016-09-21
John | Action1 | 2017-02-20
(2 rows)
In old-style SQL (with optional extra table and column aliases):
SELECT ac.name, ac.action, ac.time
FROM action_times ac,
(SELECT tmp.name AS max_name, MAX(tmp.time) AS max_time
FROM action_times tmp GROUP BY tmp.name) mx
WHERE ac.name = mx.max_name
AND ac.time = mx.max_time;
The idea is to join your table to an aggregated version of itself and get extra info (action in this case). Those optional extra column and table aliases might make it easier to see what's going on.
Note that in this typical GROUP BY for a SELECT statement with an aggregation function (MAX() in this case) you should GROUP BY all non-aggregated columns (there is just one of them, name, in this case).
[setup stuff:-
create table action_times (name varchar(10), action varchar(10), time varchar(10));
insert into action_times values ('John', 'Action1', '2017-02-20');
insert into action_times values ('John', 'Action2', '2017-02-10');
insert into action_times values ('Mark', 'Action3', '2016-09-21');
insert into action_times values ('Mark', 'Action4', '2016-03-11');
Quick check:
select * from action_times order by name, time;
name | action | time
------+---------+------------
John | Action2 | 2017-02-10
John | Action1 | 2017-02-20
Mark | Action4 | 2016-03-11
Mark | Action3 | 2016-09-21
(4 rows)
yup, looks ok]
