SQL return the highest ranked record against two criteria

user_15237534 0
Viewed 19

I'm struggling to write a query that will rank records against two criteria and return the first one that matches.

For example, this is a simplified version of my data:

Subscription ID Status Start date User
1 Cancelled 01/01/2021 12
2 On hold 02/01/2021 12
3 On hold 03/01/2021 12
4 Cancelled 04/01/2021 15
5 Active 05/01/2021 15
6 On hold 06/01/2021 15
7 Cancelled 07/01/2021 15

Users can have multiple subscriptions, and I'm trying to return a single subscription for each user as a sort of 'most relevant subscription'. This is defined for each user as follows:

IF user has any active subscriptions THEN return the most recent active subscription
ELSE IF user has any on-hold subscriptions THEN return the most recent on-hold subscription
ELSE IF user has any cancelled subscriptions THEN return the most recent cancelled subscription

So for the above data I'd like to see

User Subscription ID Status Start date
12 3 On hold 03/01/2021
15 5 Active 05/01/2021

Any help appreciated

mysql sql database
1 Answers

You can use row_number():

select s.*
from (select s.*,
             row_number() over (partition by user
                                order by (status = 'Active') desc,
                                         (status = 'On hold') desc,
                                         (status = 'Cancelled') desc,
                                         start_date desc
                               ) as seqnum
      from subscriptions s
     ) s
where seqnum = 1;
user_11440350
Related