CODEWITHSUNDEEP
sql-serverazureazure-synapse
Ruchit Rami
Ruchit Rami

Reputation: 2282

SQL Data warehouse: Incorrect syntax near 'OFFSET'

I am trying out few functionalities in SQL data warehouse. I was trying out OFFSET with sample dataset provided with SQL data warehouse. I got error: Incorrect syntax near 'OFFSET'.

I have checked the database compatibility version and it is 130(SQL Server 2016). Following is the query I am trying:

SELECT [SalesQuotaKey]
      ,[EmployeeKey]
      ,[DateKey]
      ,[CalendarYear]
      ,[CalendarQuarter]
      ,[SalesAmountQuota]
  FROM [dbo].[FactSalesQuota]
  order by [SalesAmountQuota] desc
  OFFSET 0 ROWS  
    FETCH NEXT 10 ROWS ONLY;

Is there anything I am missing in query or do I need to do any settings change to enable using OFFSET?

Upvotes: 2

Views: 2619

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28930

As per docs Offset Fetch is not supported in AzureDW

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse

[ ORDER BY
{
order_by_expression
[ ASC | DESC ]
} [ ,...n ]
]

You can simulate OFFSET Fetch in DW using RowNumber like below

select * from 
(
SELECT [SalesQuotaKey]
      ,[EmployeeKey]
      ,[DateKey]
      ,[CalendarYear]
      ,[CalendarQuarter]
      ,[SalesAmountQuota],
Row_number() over (order by salesamount desc) as rownum
  FROM [dbo].[FactSalesQuota]

)tbl
where rownum between 1 and 10

Upvotes: 2

Ron Dunn
Ron Dunn

Reputation: 3078

If you only ever want the first 'n' rows, use the TOP clause.

What is the use case where you'd want to page the rows? ASDW isn't really designed for this query type, performance will suffer.

Upvotes: 1

Related Questions