MovieID Title WatchCountIs there any way to simplify this convoluted query?
1 Gone with the Wind 10
3 Green Hornet 6
5 Superman 15
create table #Movie (
MovieID int primary key,
Format varchar(10),
WatchCount int
);
create table #Title (
MovieID int,
Title varchar(50)
);
insert #Movie values (1,'DVD',10);
insert #Movie values (2,'DVD',5);
insert #Movie values (3,'Blu-ray',6);
insert #Movie values (4,'DVD',15);
insert #Movie values (5,'Blu-ray',15);
insert #Title values (1,'Gone with the Wind');
insert #Title values (2,'Green Hornet');
insert #Title values (3,'Green Hornet');
insert #Title values (4,'Superman');
insert #Title values (5,'Superman');
select Max(m.MovieID) as MovieID,t.Title,max(m.WatchCount) as WatchCount from (
select t.Title, max(m.WatchCount) as MaxWatch
from #Movie m
inner join #Title t on t.MovieID=m.MovieID
group by t.Title
) MoviesWithMaxWatchCount
inner join #Movie m on m.WatchCount=MoviesWithMaxWatchCount.MaxWatch
inner join #Title t on t.MovieID=m.MovieID and t.Title=MoviesWithMaxWatchCount.Title
group by t.Title
ASKER
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY
select Max(m.MovieID) as MovieID,t.Title,max(m.Watc
from #Movie m
inner join #Title t
on t.MovieID=m.MovieID
group by t.Title