Compare the runtime of the movies with the maximum runtime of all the movies using SQL windows funct-CodePudding

There is a table movie.

movie	year	runtime
Titanic	1999	240
Avengers	2000	300

The question is -

Compare the runtime of the movies with the maximum runtime of all the movies using SQL windows function.

I can't see any scope of using window functions in this, but at the same time, this question is not getting solved with simple queries.

CodePudding user response：

You didn't specify a RDBMS, so I'll use SQL Server for my example.

with movie (
  movie,
  year,
  runtime
) as (
  select *
  from (
  values 
    ('Titanic', 1999, 240)
  , ('Avengers', 2000, 300)
  ) a (movie, year, runtime)
)
select movie
  , year
  , runtime
  , max(runtime) over (partition by 1)
from movie

CodePudding user response：

Maybe this could work...

with max_runtime as (
  select max(runtime) as runtime from movie
)
select m.movie, m.year, m.runtime, mr.runtime - m.runtime as diff
from movie m
  join max_runtime mr
    on 1=1