--SQL Server 2012 WITH CTE_SoldProduct AS ( SELECT SUM(Quantity) QNT,ProductID FROM dbo.TBL_Sales GROUP BY ProductID ) SELECT P.NAME ,P.Price ,SP.QNT FROM CTE_SoldProduct SP INNER JOIN dbo.TBL_Product p ON SP.ProductID = p.ID ORDER BY SP.QNT DESC offset 1 rows FETCH NEXT 2 ROWS ONLY; --SQL Server 2008 WITH CTE_SoldProduct AS ( SELECT ROW_NUMBER() OVER(ORDER BY SUM(Quantity) DESC) AS num, SUM(Quantity) QNT,ProductID FROM dbo.TBL_Sales GROUP BY ProductID ) SELECT P.NAME ,P.Price ,SP.QNT FROM CTE_SoldProduct SP INNER JOIN dbo.TBL_Product p ON SP.ProductID = p.ID WHERE num >=2
Categories: SQL Server, SQL Server Development
Leave a Reply