We help IT Professionals succeed at work.
Get Started

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

466 Views
Last Modified: 2012-05-11
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
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE