T-SQL: How to obtain a primary key of Max(column) row; Please simplify this query

ZuZuPetals
ZuZuPetals used Ask the Experts™
on
I conjured the example below to help me explain the problem.  I need to join a row in the one table that has a maximum value to another table.

This would be easily solved if SQL Server had a MaxPK() function that instead of returning the max value instead returned the primary key of the row having maximum value.

My contrived example seeks to generate the maximum count a movie has been watched between different versions of the same movie (DVD vs. Blu-ray), where the title just happens to be stored in a separate table:
MovieID    Title                           WatchCount
1                Gone with the Wind      10
3                Green Hornet                    6
5                Superman                       15
Is there any way to simplify this convoluted query?
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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
what about:
select *
from (
	select t.Title, m.WatchCount, row_number() over (partition by t.Title order by m.WatchCount desc )rn                          
		from #Movie m
	        join #Title t on t.MovieID=m.MovieID

    ) sq
where sq.rn = 1

Open in new window

Try this:

select Max(m.MovieID) as MovieID,t.Title,max(m.WatchCount) as WatchCount
from #Movie m
inner join #Title t
on t.MovieID=m.MovieID
group by t.Title

Author

Commented:
Thank you prajapati84, but your solution doesn't return the correct MovieID associated with the maximum WatchCount.  Instead it just returns the MovieID that has the maximum value which may or may not be correct.  In your case the MovieID can't be used to join to pull out other values.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial