--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

Tags: FETCH NEXT ROWS, OFFSET, SQL Server 2012