troubleshooting Question

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

Avatar of ZuZuPetals
ZuZuPetalsFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
3 Comments1 Solution471 ViewsLast Modified:
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

ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros