CODEWITHSUNDEEP
sql
Javi DR
Javi DR

Reputation: 165

SQL: How to select first element based on a condition?

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

Upvotes: 0

Views: 13643

Answers (3)

Shruti
Shruti

Reputation: 190

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]

Upvotes: 1

Adrian Redgers
Adrian Redgers

Reputation: 394

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]

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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.

Upvotes: 3

Related Questions