
--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
Like this:
Like Loading...
Related