CODEWITHSUNDEEP
mysqlsql
paparas
paparas

Reputation: 9

Return the max value based on two columns sql

Let's say I have a table with the following schema:

User_id | Rating | Movie_id | Movie_Popularity
 1          2         34          100
 1          1         36          120
 1          2         39          110 
 2          3         34          105

What I would like is for each user find his max and his min rating and in case of a tie select the movie that has the highest popularity(suppose these are all distinct).

In this example the result would be:

User_id | Max_rating | Min_rating | Max_rating_movie_id | Min_rating_movie_id
     1          2         1                 39                  36
     2          3         3                 34                  34

I have tried sorting this table with both rating and movie popularity but I am able to extract only the max or the min value.

How could this be done using sql?

Any advice would be helpful.

Thanks in advance

Upvotes: 0

Views: 302

Answers (3)

forpas
forpas

Reputation: 164194

There is no need for subqueries.
You can do it with window functions MIN(), MAX() and FIRST_VALUE():

SELECT DISTINCT User_id,
       MAX(Rating) OVER (PARTITION BY User_id) Max_rating,
       MIN(Rating) OVER (PARTITION BY User_id) Min_rating,
       FIRST_VALUE(Movie_id) OVER (PARTITION BY User_id ORDER BY Rating DESC, Movie_Popularity DESC) Max_rating_movie_id,
       FIRST_VALUE(Movie_id) OVER (PARTITION BY User_id ORDER BY Rating, Movie_Popularity DESC) Min_rating_movie_id
FROM tablename

See the demo.
Results:

User_id Max_rating Min_rating Max_rating_movie_id Min_rating_movie_id
1 2 1 39 36
2 3 3 34 34

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

One method uses window functions with aggregation:

select user_id, max(Movie_Popularity), min(Movie_Popularity),
       max(case when seqnum_desc = 1 then movie_id end),
       max(case when seqnum_asc = 1 then movie_id end)
from (select t.*,
             row_number() over (partition by user_id order by Movie_Popularity asc) as seqnum_asc,
             row_number() over (partition by user_id order by Movie_Popularity desc) as seqnum_desc
      from t
     ) t
group by user_id;

Upvotes: 1

jane doe
jane doe

Reputation: 34

Hi, you can use row_number() over (partition by .. order by ...asc) .

This a query sample:

   Select user_id, movie_id, row_number() over (partition by user_id order by rating desc) as max_rating, row_number() over (partition by user_id order by rating asc) as min_rating

Then you can use it as a sub query & select by where on max_rating & min_rating equals to 1.

Upvotes: 1

Related Questions