CODEWITHSUNDEEP
sqlhive
tetrathinker
tetrathinker

Reputation: 13

sql select priority based on multiple columns

Here is some sample data. I'm trying to get a single record for each UserID for the most recent activity date. If the a user watched more than one movie on a given date, record should be selected based on priority associated with movie name

UserID MovieName ActivityDate
1       MOV1    2015-02-12
2       MOV2    2015-04-22
1       MOV3    2015-03-16
3       MOV1    2015-06-23
2       MOV5    2016-01-01
2       MOVH    2016-01-01

Priority associated with movie name -

MOV1 > MOV2 > MOV3 > MOV5 > MOVH

Expected Result : 

UserID MovieName ActivityDate
1       MOV3    2015-03-16
2       MOV5    2016-01-01
3       MOV1    2015-06-23

I've tried the combination of GROUP BY and CASE, but I'm pretty sure there could be a better way. Any help is appreciated.

Upvotes: 1

Views: 8034

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

The right answer is row_number(), but you need to be careful about the order by:

select m.*
from (select m.*,
             row_number() over (partition by UserId
                                order by ActivityDate desc,
                                         (case MovieName
                                               when 'MOV1' then 1
                                               when 'MOV2' then 2
                                               when 'MOV3' then 3
                                               when 'MOV5' then 4
                                               when 'MOVH' then 5
                                               else 999
                                          end)
                               ) as seqnum

      from movies m
     ) m
where seqnum = 1;

Upvotes: 0

artm
artm

Reputation: 8584

select *
from (
select *
, row_number() OVER (partition by id order by MovieName desc, ActivityDate desc) as rnk
from movies) m
where m.rnk = 1

Upvotes: 2

Related Questions