Problems with GROUP BY clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problems with GROUP BY clause

Author

Topic

txverm
Starting Member

3 Posts

Posted - 2009-05-06 : 13:32:48

Hi,

I would appreciate some help with the following query.

I have two tables ITEMS and CATEGORIES.

ITEMS has

Item_ID
Item_Name
Category_ID
Price

CATEGORIES has

Category_ID
Category_Name

Now I want to list the name, category name and price for the items with the highest price FOR EACH category, using only one query.

I currently have this:

SELECT Item_Name
, Category_Name
, Max(Price)
FROM ITEMS
, CATEGORIES
WHERE ITEMS.Category_ID = CATEGORIES.Category_ID
GROUP BY ITEMS.Category_ID

It fails for obvious reasons. I did not include all the items in the SELECT clause as part of the GROUP BY clause:

Column 'ITEMS.Item_Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

But how to work around this?

Any help would be appreciated!

Thanks,
Tim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-06 : 13:50:26

Are you using SQL Server 2000 or SQL Server 2005?

E 12°55'05.63"
N 56°04'39.26"

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-05-06 : 13:54:52

SELECT c.item_name, a.category_name, b.price FROM [categories] a INNER JOIN (SELECT category_id, item_id, MAX(price) AS price FROM [items] GROUP BY category_id, item_id) b ON a.category_id = b.category_id INNER JOIN [items] c ON b.item_id = c.item_id

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-06 : 13:58:31

Only the item with highest price for each category was a request from OP, I believe.
With you suggestion above you will get all items and all categories with the max price for the combination.

E 12°55'05.63"
N 56°04'39.26"

txverm
Starting Member

3 Posts

Posted - 2009-05-06 : 15:18:37

Thanks for the replies.

@Peso

That's correct. I use SQL-Server 2005 BTW.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-06 : 16:14:26

quote:
Originally posted by txverm

That's correct. I use SQL-Server 2005 BTW.

Thank you.
Try this

SELECT	Item_Name,
	Category_Name,
	Price
FROM	(
		SELECT		i.Item_Name,
				c.Category_Name,
				i.Price,
				ROW_NUMBER() OVER (PARTITION BY i.Category_ID ORDER BY i.Price DESC) AS recID
		FROM		Items AS i
		INNER JOIN	Categories AS c ON c.Category_ID = i.Category_ID
	) AS d
WHERE	recID = 1

I hope WhiteFang sees this and learns to get as much information as possible first, before suggesting something OP does not want nor is helped by.

E 12°55'05.63"
N 56°04'39.26"

txverm
Starting Member

3 Posts

Posted - 2009-05-07 : 05:48:18

Thanks Peso, it works fine.

Just altered it a bit using RANK, so that I get multiple items per category if they are tied for highest price:

SELECT Item_Name,
Category_Name,
Price
FROM (
SELECT i.Item_Name,
c.Category_Name,
i.Price,
RANK() OVER (PARTITION BY i.Category_ID ORDER BY i.Price DESC) AS RankID
FROM Items AS i
INNER JOIN Categories AS c ON c.Category_ID = i.Category_ID
) AS d
WHERE RankID = 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-07 : 05:56:00

You're welcome.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources