SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 On Northwind db, all rows coming with grouping		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

neu84
Starting Member

2 Posts
Posted - 01/23/2013 :  16:17:44  Show Profile  Reply with Quote
hi,
I'm trying to write a query. I want to display that which product most sold in related year. And i want to show like this,
YEAR, PRODUCTNAME, MAXQUANTITY
1996 Gorgonzola Telino 444
1997 Gnocchi di nonna Alice 971
1998 Konbu 659
But my code (following code) is listing all products' quantity, but i want to see only maximum products in related year.

SELECT ODATE,ProductName,MAXQ FROM Products P INNER JOIN
(
SELECT T.ODATE,T.ProductID,MAX(T.QUANT) AS MAXQ
FROM
( 
	SELECT  YEAR(O.OrderDate) ODATE,ProductID,SUM(QUANTITY) AS QUANT FROM [Order Details] OD
	INNER JOIN Orders O ON O.OrderID=OD.OrderID
	GROUP BY YEAR(O.OrderDate),ProductID
) T
group by T.ODATE,T.ProductID 

) T2 ON P.ProductID=T2.ProductID
ORDER BY ODATE ASC,MAXQ DESC


But if i add 
TOP 1 with ties
to first select, its getting only one year info.
YEAR, PRODUCTNAME, MAXQUANTITY
1996 Gorgonzola Telino 444

How can i solve that problem? Can u advice me something please?

jimf
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 01/23/2013 :  17:44:18  Show Profile  Reply with Quote
I think you can do this in SQL 2005

SELECT ODATE,ProductID,QUANT
FROM
(
SELECT YEAR(O.OrderDate) ODATE,ProductID,SUM(QUANTITY) AS QUANT
,[rown] = ROW_NUMBER() over(order by SUM(QUANTITY) desc)
FROM [Order Details] OD
INNER JOIN Orders O ON O.OrderID=OD.OrderID
GROUP BY YEAR(O.OrderDate),ProductID
) t1
WHERE rown = 1

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1730 Posts
Posted - 01/23/2013 :  17:47:58  Show Profile  Reply with Quote
One way would be to change the inner join to a CROSS APPLY like shown below (I don't have Northwind database, so couldn't test this) 
SELECT ODATE,
       ProductName,
       MAXQ
FROM   Products P
       CROSS APPLY (
                SELECT TOP (1) T.ODATE,
                       T.ProductID,
                       MAX(T.QUANT) AS MAXQ
                FROM   (
                           SELECT YEAR(O.OrderDate) ODATE,
                                  ProductID,
                                  SUM(QUANTITY) AS QUANT
                           FROM   [Order Details] OD
                                  INNER JOIN Orders O
                                       ON  O.OrderID = OD.OrderID
                           GROUP BY
                                  YEAR(O.OrderDate),
                                  ProductID
                       ) T
                WHERE P.ProductID = T.ProductID
                GROUP BY
                       T.ODATE,
                       T.ProductID
                ORDER BY 
					MAX(T.QUANT) DESC
            ) T2
            
ORDER BY
       ODATE ASC,
       MAXQ DESC
Go to Top of Page

neu84
Starting Member

2 Posts
Posted - 01/24/2013 :  06:35:07  Show Profile  Reply with Quote
Thank you so much to both of you frieds. :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000