How to use OFFSET and FETCH NEXT ROWS in SQL Server 2012?

sql2008

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s