Top Clause with GROUP BY Clause

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

SQL Server 2005 Forums

Transact-SQL (2005)

Top Clause with GROUP BY Clause

Author

Topic

waqas
Starting Member

5 Posts

Posted - 2008-04-03 : 09:31:45

How Can I use Top Clause with GROUP BY clause?

Here is my simple problem.

I have two tables

Categories
Products

I want to know Top 5 Products in CategoryID 1,2,3,4,5

Resultset should contain 25 Rows ( 5 top products from each category )

I hope someone will help me soon.
Its urngent

thanks in advance

regards
Waqas

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-03 : 09:45:49

Point 2
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-03 : 10:12:55

[code]SELECT t.*
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY ProductID DESC) AS RowNo,
*
FROM Categories c
INNER JOIN Products p
ON p.ProductID=c.ProductID
)t
WHERE t.RowNo <=5[/code]

waqas
Starting Member

5 Posts

Posted - 2008-04-05 : 21:05:24

Hi visakh

I tried to use your query but it doesn't work. Are you missing any thing in the query ?

Thanks for your earlier reply with queyr but can you please check it and resent if something is missing in query.

have a nice day

regards
Waqas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-06 : 02:41:39

quote:
Originally posted by waqas

Hi visakh

I tried to use your query but it doesn't work. Are you missing any thing in the query ?

Thanks for your earlier reply with queyr but can you please check it and resent if something is missing in query.

have a nice day

regards
Waqas

Can you post what error message you got? Also make sure your server is SQL 2005 and you have used a compatability level of 90 using sp_dbcmptlevel command. ROW_NUMBER works only in 2005 with compatibilty level 90.

waqas
Starting Member

5 Posts

Posted - 2008-04-06 : 12:20:46

I am using SQL Server 2005 and Compatibility Level is also 90.
I think error is in the query because if you see the error below it is something related to column name or alias. See the error below I am getting in my database.

Msg 207, Level 16, State 1, Line 7
Invalid column name 'ProductID'.
Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'CategoryID'.
Msg 8156, Level 16, State 1, Line 3
The column 'CategoryID' was specified multiple times for 't'.

I am using Northwind database so Table Products has column ProductID and CategoryID is also valid column name in Categories Table.

Please check your query again, I am also trying to figure out what is error in that time.

thanks
Waqas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-06 : 12:41:51

Can you provide the structure of your two tables? That should make it more clear.

waqas
Starting Member

5 Posts

Posted - 2008-04-06 : 14:34:37

Thanks for quick Reply

As I just need example query so I am using Northwind database tables for testing query. Later I will change it according to my requirements of actual project.

Here are two tables

Categories (Table)
CategoryID INT (PK)
CategoryName NVARCHAR(15)

Products (Table)
ProductID INT
ProductName NVARCHAR(40)
UnitPrice MONEY
CategoryID INT (FK)

I just want top 5 products of each category as one result set. For example if I have 4 categories so result set should display 20 products ( 5 top products of 4 categories ).

If its easy to do in T-SQL Procedure than it will also be ok with me as i am using stored procedures already.

Thanks for all your replies once again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-06 : 14:47:49

Change like this. I've assumed you want top 5 products based on decreasing order of price else change UnitPrice filed to your desired field.

SELECT t.CategoryName,t.ProductName
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY c.CategoryID ORDER BY UnitPrice DESC) AS RowNo,
c.CategoryID,
c.CategoryName, 
p.ProductID,
p.ProductName
FROM Categories c
INNER JOIN Products p
ON p.CategoryID =c.CategoryID 
)t
WHERE t.RowNo <=5

waqas
Starting Member

5 Posts

Posted - 2008-04-06 : 15:03:25

Hi Visakh

I dont have words to say you thanks for your quick replies and finally solving my problem. I tested your query on Northwind database and now its working. I also modified it for my project according to different tables and fiels and it works there as well. I have to admit you really help me alot to solve my problem. Thanks for all your help.

Have a nice day

Regards
Waqas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-07 : 01:47:47

quote:
Originally posted by waqas

Hi Visakh

I dont have words to say you thanks for your quick replies and finally solving my problem. I tested your query on Northwind database and now its working. I also modified it for my project according to different tables and fiels and it works there as well. I have to admit you really help me alot to solve my problem. Thanks for all your help.

Have a nice day

Regards
Waqas

You are welcome

. I would suggest you reading this wonderful article by Madhi on the usage of ROW_NUMBER() function in SQL 2005:-

[url]http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources