CREATE TABLE #tmp(
	FilmName VARCHAR(50), 
	Category VARCHAR(50),
	MostPopular INT
)

INSERT INTO #tmp
SELECT 'Soylent Yellow', 'Sci-fi', 10 UNION ALL 
SELECT 'The Life Of Bob', 'Thriller', 10 UNION ALL 
SELECT 'The Maltese Poodle', 'Thriller', 10 UNION ALL 
SELECT 'On Golden Puddle', 'Romance', 9 UNION ALL 
SELECT 'The Good, the Bad, and the Facially Challenged', 'Historical', 9 UNION ALL 
SELECT 'Nightmare on Oak Street, Part 23', 'Horror', 9 UNION ALL 
SELECT 'One Flew Over the Crow''s Nest', 'Horror', 8 UNION ALL 
SELECT 'Gone With the Window Cleaner', 'Historical', 8 UNION ALL 
SELECT 'Sense and Insensitivity', 'Historical', 5 

SELECT FilmName, Category, MostPopular
FROM (
		SELECT FilmName, Category, MostPopular,
				ROW_NUMBER() OVER (PARTITION BY Category ORDER BY MostPopular DESC) AS Row 
		FROM #tmp ) z
WHERE Row = 1

DROP TABLE #tmp

quote:

---

Originally posted by AJamison

I am working on a problem where I need to find the most popular film by category. I have been able to write a query that returns the film name, category and number of times the film was ordered (using COUNT function and named 'Popularity.') I tried to use the MAX function on the Popularity column, but got an error "Cannot perform an aggregate function on an expression containing an aggregate." I think a subquery is the answer, but can't get one to work. I'm also having trouble with the "by category" part of the problem. The data I am using is as follows:

FilmName Category Most Popular
Soylent Yellow Sci-fi 10
The Life Of Bob Thriller 10
The Maltese Poodle Thriller 10
On Golden Puddle Romance 9
The Good, the Bad, and the Facially Challenged Historical 9
Nightmare on Oak Street, Part 23 Horror 9
One Flew Over the Crow's Nest Horror 8
Gone With the Window Cleaner Historical 8
Sense and Insensitivity Historical 5

Does anyone have a suggestion? Thanks in advance!

---

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by AJamison

I am working on a problem where I need to find the most popular film by category. I have been able to write a query that returns the film name, category and number of times the film was ordered (using COUNT function and named 'Popularity.') I tried to use the MAX function on the Popularity column, but got an error "Cannot perform an aggregate function on an expression containing an aggregate." I think a subquery is the answer, but can't get one to work. I'm also having trouble with the "by category" part of the problem. The data I am using is as follows:

FilmName Category Most Popular
Soylent Yellow Sci-fi 10
The Life Of Bob Thriller 10
The Maltese Poodle Thriller 10
On Golden Puddle Romance 9
The Good, the Bad, and the Facially Challenged Historical 9
Nightmare on Oak Street, Part 23 Horror 9
One Flew Over the Crow's Nest Horror 8
Gone With the Window Cleaner Historical 8
Sense and Insensitivity Historical 5

Does anyone have a suggestion? Thanks in advance!

---

Which version of SQL Server are you using?
DBA's will work from version 2005 onwards

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by DBA in the making

Does this help?

CREATE TABLE #tmp(
	FilmName VARCHAR(50), 
	Category VARCHAR(50),
	MostPopular INT
)

INSERT INTO #tmp
SELECT 'Soylent Yellow', 'Sci-fi', 10 UNION ALL 
SELECT 'The Life Of Bob', 'Thriller', 10 UNION ALL 
SELECT 'The Maltese Poodle', 'Thriller', 10 UNION ALL 
SELECT 'On Golden Puddle', 'Romance', 9 UNION ALL 
SELECT 'The Good, the Bad, and the Facially Challenged', 'Historical', 9 UNION ALL 
SELECT 'Nightmare on Oak Street, Part 23', 'Horror', 9 UNION ALL 
SELECT 'One Flew Over the Crow''s Nest', 'Horror', 8 UNION ALL 
SELECT 'Gone With the Window Cleaner', 'Historical', 8 UNION ALL 
SELECT 'Sense and Insensitivity', 'Historical', 5 

SELECT FilmName, Category, MostPopular
FROM (
		SELECT FilmName, Category, MostPopular,
				ROW_NUMBER() OVER (PARTITION BY Category ORDER BY MostPopular DESC) AS Row 
		FROM #tmp ) z
WHERE Row = 1

DROP TABLE #tmp

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

---