CODEWITHSUNDEEP
sql-servert-sql
RunningOverTheHill
RunningOverTheHill

Reputation: 55

Reducing rows based on a grouped MAX()

Scratching my head for a day on this.

Need to reduce a list of films and genres to a list of films and genres based on the grouped max() views associated with the film.

So transforming 'a' to 'b' here with T-SQL
(Purple rows are the valid ones that will make it output table: Image )

+---------+--------------+-------+
|  Title  |    Genre     | Views |
+---------+--------------+-------+
| Mad Max | Mockumentary |     1 |
| Mad Max | Sci-fi       |   169 |
| Mad Max | Documentary  |    32 |
| Titanic | Drama        |     6 |
| E.T.    | Sci-fi       |    34 |
| E.T.    | Sci-fi       |     2 |
| E.T.    | Horror       |     1 |
| Taken   | Triller      |    60 |
| Taken   | Crime Drama  |     2 |
| Taken   | Triller      |    40 |
| Taken   | Crime Drama  |    15 |
+---------+--------------+-------+

Expected outcome

+---------+---------+-------+
|  Title  |  Genre  | Views |
+---------+---------+-------+
| Mad Max | Sci-fi  |   169 |
| Titanic | Drama   |     6 |
| E.T.    | Sci-fi  |    36 |
| Taken   | Triller |   100 |
+---------+---------+-------+

Upvotes: 0

Views: 47

Answers (1)

DxTx
DxTx

Reputation: 3357

Try this one...

SELECT title, genre, views
FROM   (SELECT title, 
               genre, 
               Sum(views) AS views, 
               ROW_NUMBER() OVER (PARTITION BY title ORDER BY Sum(views) DESC) AS ranks 
        FROM   tablename 
        GROUP  BY title, genre) tmp 
WHERE ranks < 2

Output

+---------+---------+-------+
|  title  |  genre  | views |
+---------+---------+-------+
| E.T.    | Sci-fi  |    36 |
| Mad Max | Sci-fi  |   169 |
| Taken   | Triller |   100 |
| Titanic | Drama   |     6 |
+---------+---------+-------+

Online Demo: http://www.sqlfiddle.com/#!18/e34fe/1/0

Upvotes: 2

Related Questions