Top 1 Profitable movie for each decade

Question

I need help with SQL Server query. I need to extract only the top 1 profitable movie for each decade.

Suppose there are 20 distinct decades and I only want to extract the top 1 profitable movie for each decade. Can someone help me with the query?

I have attached the screen shot for the reference. My result shows all the profitable movies for each decade. I only want the top 1 profitable movie for each decade.

For reference enter image description here

Select 
    decade, Movie_Title, Profit 
from 
    DW.IMDB_MOVIE
group by 
    decade, Movie_Title, profit
order by 
    decade, profit desc

This type of question has been answered many times on Stack Overflow. See the [tag:greatest-n-per-group] tag for answers. — Bill Karwin, Jan 06 '23 at 17:42
You should be clear which database you're using. You tagged both mysql and sql-server, but these are different products and the answer to your task may be different depending on which of these database products you are really using. If you query `SELECT @@version;` you will find out what you are using. — Bill Karwin, Jan 06 '23 at 17:42
Thanks, @BillKarwin for the reply. I am using SQL Server database. — vsoni, Jan 06 '23 at 17:50

score 3 · Answer 1 · answered Jan 06 '23 at 17:53

One option is using WITH TIES in concert with the window function row_number()

Example

 Select top 1 with ties *
  From  DW.IMDB_MOVIE 
  Order by row_number() over (partition by decade order by profit desc)

Or a nudge more performant

with cte as (
Select *
      ,RN = row_number() over (partition by decade order by profit desc)
From  DW.IMDB_MOVIE 
)
Select * 
 From  cte 
 Where RN=1

Top 1 Profitable movie for each decade

1 Answers1