Row number in sql group by query

Hi,

I like to add row number to a query. My sql is like this:
SELECT TOP (5) od.productID, COUNT(od.productID) AS quantity, b.shortProductText, b.product, b.salesPrice, b.articleNo, ac.articleCategory 
FROM orders AS o INNER JOIN orderDetail AS od ON o.orderID = od.orderID 
INNER JOIN bklProducts AS b ON od.productID = b.productID 
INNER JOIN bklArticleCategory AS ac ON b.articleCategoryID = ac.articleCategoryID WHERE (o.orderStatusID > 1) 
AND (o.orderDate BETWEEN DATEADD(week, - 4, o.orderDate) AND GETDATE()) AND (b.activeWebshop = 1) 
GROUP BY od.productID, b.product,b.shortProductText, b.salesPrice, b.articleNo, ac.articleCategory 
ORDER BY antal DESC

Open in new window


The output is:
productID        quantity         shortProductText                                   product                       salesPrice       articleNo        articleCategory
335                51                 Stefan Jernsand & Jenny Rydén           Jag ser ett land               125,00               cdl10         CD
396                19                 ÄRLIGT TALAT med Alf Svensso     Alf Svensson               125,00               dvdÄr2         DVD
399                13                 Från barnslav till missionär                  Abbi Åkvist                       125,00               dvdÄr2         DVD
306                12                 Stefan Jernsand live                          En ny ton från himlen       125,00               cdl3                 CD

I woudl like it to be:
row   productID        quantity         shortProductText                                   product                       salesPrice       articleNo        articleCategory
1       335                         51                 Stefan Jernsand & Jenny Rydén           Jag ser ett land               125,00               cdl10         CD
2       396                         19                 ÄRLIGT TALAT med Alf Svensso     Alf Svensson               125,00               dvdÄr2         DVD
3       399                         13                 Från barnslav till missionär                  Abbi Åkvist                       125,00               dvdÄr2         DVD
4       306                         12                 Stefan Jernsand live                          En ny ton från himlen       125,00               cdl3                 CD

Tkanks for help!

Peter
Peter NordbergIT ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
Try this:
SELECT TOP (5) ROW_NUMBER() OVER(ORDER BY antal DESC) row, od.productID, COUNT(od.productID) AS quantity, b.shortProductText, b.product, b.salesPrice, b.articleNo, ac.articleCategory 
FROM orders AS o INNER JOIN orderDetail AS od ON o.orderID = od.orderID 
INNER JOIN bklProducts AS b ON od.productID = b.productID 
INNER JOIN bklArticleCategory AS ac ON b.articleCategoryID = ac.articleCategoryID WHERE (o.orderStatusID > 1) 
AND (o.orderDate BETWEEN DATEADD(week, - 4, o.orderDate) AND GETDATE()) AND (b.activeWebshop = 1) 
GROUP BY od.productID, b.product,b.shortProductText, b.salesPrice, b.articleNo, ac.articleCategory 
ORDER BY antal DESC

Open in new window

Peter NordbergIT ManagerAuthor Commented:
Hi,

I get this error

Msg 207, Level 16, State 1, Line 1
Invalid column name 'antal'.

Peter
Peter NordbergIT ManagerAuthor Commented:
This is the query I used:

SELECT TOP (5) ROW_NUMBER() OVER(ORDER BY antal DESC) row, od.productID, COUNT(od.productID) AS antal, b.shortProductText, b.product, b.salesPrice, b.articleNo, ac.articleCategory 
FROM orders AS o INNER JOIN orderDetail AS od ON o.orderID = od.orderID 
INNER JOIN bklProducts AS b ON od.productID = b.productID 
INNER JOIN bklArticleCategory AS ac ON b.articleCategoryID = ac.articleCategoryID WHERE (o.orderStatusID > 1) 
AND (o.orderDate BETWEEN DATEADD(week, - 4, o.orderDate) AND GETDATE()) AND (b.activeWebshop = 1) 
GROUP BY od.productID, b.product,b.shortProductText, b.salesPrice, b.articleNo, ac.articleCategory 
ORDER BY antal DESC

Open in new window

MlandaTCommented:
Sorry... I hadn't noticed that antal is a COUNT(od.productID). Please try this:
SELECT TOP (5) ROW_NUMBER() OVER(ORDER BY COUNT(od.productID) DESC) row, od.productID, COUNT(od.productID) AS antal, b.shortProductText, b.product, b.salesPrice, b.articleNo, ac.articleCategory 
FROM orders AS o INNER JOIN orderDetail AS od ON o.orderID = od.orderID 
INNER JOIN bklProducts AS b ON od.productID = b.productID 
INNER JOIN bklArticleCategory AS ac ON b.articleCategoryID = ac.articleCategoryID WHERE (o.orderStatusID > 1) 
AND (o.orderDate BETWEEN DATEADD(week, - 4, o.orderDate) AND GETDATE()) AND (b.activeWebshop = 1) 
GROUP BY od.productID, b.product,b.shortProductText, b.salesPrice, b.articleNo, ac.articleCategory 
ORDER BY antal DESC

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Peter NordbergIT ManagerAuthor Commented:
Thanks! Works very good!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.