Avatar of ZuZuPetals
ZuZuPetalsFlag for United States of America

asked on 

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

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

Microsoft SQL ServerMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
ZuZuPetals
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of prajapati84
prajapati84
Flag of India image

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
Avatar of ZuZuPetals
ZuZuPetals
Flag of United States of America image

ASKER

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.
Microsoft SQL Server
Microsoft SQL Server

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.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo