CODEWITHSUNDEEP
mysqlsqldategreatest-n-per-groupmysql-5.7
Brad
Brad

Reputation: 12262

mysql join table and search for most recent record on where clause

I have two tables

Below just shows you how the two tables are related. I want to return subscriptions that expire after 1565827199, but it needs to check against each user's most recent subscription.

select 
u.id 
from users u 
join subscriptions s on u.id s.userId 
where s.currentPeriodEnd > 1565827199 
ORDER BY u.lastName ASC

A user may have multiple subscriptions in the subscriptions table. What I need to do is modify the query above, so it checks against that user's most recent subscription and not the first one it finds.

select * from subscriptions ORDER BY currentPeriodEnd DESC LIMIT 1

I've tried a few different things (alias table, sub query) I found elsewhere on stackoverflow without any luck.

Upvotes: 0

Views: 803

Answers (2)

Barmar
Barmar

Reputation: 781300

Join with a subquery that gets the latest time for each user, and filters it down to just the ones after your specified timestamp.

select u.id 
from users u 
join (
    select userid
    FROM subscriptions
    GROUP BY userid
    HAVING MAX(currentPeriodEnd) > 1565827199 
) s ON s.userid = u.id
ORDER BY u.lastName ASC

Upvotes: 1

GMB
GMB

Reputation: 222512

You can filter with a correlated subquery, like so:

select u.*, s.*
from users u 
inner join subscriptions s on u.id = s.userId 
where s.currentPeriodEnd = (
    select max(s1.currentPeriodEnd) 
    from subscriptions s1 
    where s1.userId = u.id and s1.currentPeriodEnd > 1565827199
)
order by u.lastName

For performance, consider an index on subscriptions(userId, currentPeriodEnd).

Alternatively, if you are running MySQL 8.0, you can use row_number():

select *
from (
    select 
        u.*, 
        s.*, 
        row_number() over(partition by u.id order by s.currentPeriodEnd desc)
    from users u 
    inner join subscriptions s on u.id = s.userId 
    where s.currentPeriodEnd > 1565827199
) t
where rn = 1
order by lastName

Upvotes: 1

Related Questions