How to have OFFSET/FETCH and WITH TIES together?

Question

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

Answer 1

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