Find rows of table with related max date row column matching value?

Question

Wow this question is really hard to formulate succinctly. So, here's the data:

Person:
+----+---------+
| ID | Name    |
+----+---------+
|  1 | Bob     |
|  2 | Alice   |
|  3 | Greg    |
|  4 | Lisa    |
+----+---------+

Activity:
+----+----------+------------+----------+
| ID | PersonID | Date       | Activity |
+----+----------+------------+----------+
|  1 | 1        | 2017-03-01 | foo      |
|  2 | 1        | 2017-03-02 | bar      |
|  3 | 2        | 2016-12-01 | foo      |
|  4 | 3        | 2017-01-15 | foo      |
+----+----------+------------+----------+

I want to return all Person rows whose most-recent Activity is foo.

Return:
+----+---------+
| ID | Name    |
+----+---------+
|  2 | Alice   |
|  3 | Greg    |
+----+---------+

Thanks!

I'm using SQLite, but could use MySQL if absolutely necessary. I'm actually doing this via flask-sqlalchemy, and I could certainly filter it in code, but I feel like SQL will be faster. — Rick, Mar 17 '17 at 08:50
I tried modifying this answer, but can't get it to work quite right: http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column — Rick, Mar 17 '17 at 08:51

JohnHC · Accepted Answer · 2017-03-17T09:28:31.617

MySQL

select P3.*
from 
(
select PersonID, max(Date) as mDate
from Activity
group by PersonID
) a1
inner join Activity A2
  on A2.PersonID = A1.PersonID
  and A2.Date = A1.mDate
inner join Person P3
  on P3.ID = A2.PersonID
where A2.Activity = 'Foo'
and not exists (select 1 -- This is for those who did both on one day
                from Activity A4 
                where A4.Activity = 'Bar'
                and A4.PersonID = A1.PersonID
                and A4.Date = A1.mDate)

And for SQL server/Oracle (for fun)

with CTE as
(
select A1.*, row_number() over(partition by PersonID order by Date desc) as r_ord
from Activity A1
)
select P2.*
from Person P2
inner join CTE 
  on CTE.PersonID = P2.ID
where CTE.r_ord = 1
and CTE.Activity = 'Foo'

This seems to work very well, except for the case where the two activity dates are the same. The (unstated) intent was to find people who had "fooed" but not "barred". My data is a little bit dirty, I may just clean that up. — Rick, Mar 17 '17 at 09:05

score 1 · Answer 2 · answered Mar 17 '17 at 09:19

1

select * from Person where ID in 
(select PersonID from (select Top 1 * from Activity where PersonID = Person.ID order by Activity.Date desc) Tmp where Tmp.Activity <> 'bar')

answered Mar 17 '17 at 09:19

鄭有維

265
1
13

Find rows of table with related max date row column matching value?

2 Answers2