CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012sql-order-by
sqluser
sqluser

Reputation: 5672

How to have OFFSET/FETCH and WITH TIES together?

OFFSET FETCH does not support a logical equivalent to TOP (n) WITH TIES option, which ensures determinism by returning all ties.

Is there any workaround to have OFFSET FETCH and WITH TIES together?

Create statement with sample data

CREATE TABLE #tbl(Id INT, Name VARCHAR(10))

INSERT #tbl
    SELECT 1, 'a' UNION ALL
    SELECT 1, 'a' UNION ALL
    SELECT 2, 'b' UNION ALL
    SELECT 2, 'b' UNION ALL
    SELECT 2, 'b' UNION ALL
    SELECT 3, 'c' UNION ALL
    SELECT 3, 'c' UNION ALL
    SELECT 3, 'c'

TOP

SELECT TOP 3 * FROM #tbl ORDER BY Id

Id  Name
1   a
1   a
2   b

TOP n WITH TIES

SELECT TOP 3 WITH TIES * FROM #tbl ORDER BY Id

Id  Name
1   a
1   a
2   b
2   b
2   b

OFFSET FETCH

SELECT *
FROM #tbl
ORDER BY Id
OFFSET 0 ROWS
FETCH NEXT 3 ROWS ONLY

Id  Name
1   a
1   a
2   b

Probably we need to use Ranking function to achieve this??

Upvotes: 6

Views: 3067

Answers (2)

Kamil Beeli de Belfort
Kamil Beeli de Belfort

Reputation: 9

SELECT *
FROM #tbl
ORDER BY Id
OFFSET 0 ROWS
FETCH NEXT 3 ROWS WITH TIES

Upvotes: 0

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Just use them together without FETCH statement inside the CTE

;WITH cte AS
 (
  SELECT Id, Name
  FROM #tbl
  ORDER BY Id
  OFFSET 0 ROWS
  )
  SELECT TOP 3 WITH TIES *
  FROM cte
  ORDER BY Id

See SQLFiddle

Example with offset 3 SQLFiddle

;WITH cte AS
 (
  SELECT Id, Name
  FROM #tbl
  ORDER BY Id
  OFFSET 3 ROWS
  )
  SELECT TOP 3 WITH TIES *
  FROM cte
  ORDER BY Id

Upvotes: 4

Related Questions