Sql Paging result with one selected row on top

Question

A bit of context: I have a multi step form, in step1 the user must select a row in a table with pagination (filters + sorts), then he goes to step2 but at any moment he could come back to step1 and need to see the previously selected row. NB: I dont want to keep the page number in frontend storage because if the table has changed, the selected id may not be on the same page any more

The test db:

CREATE TABLE [dbo].[Table_CTE](
    [Id] [int] NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL)
    
DECLARE @i int = 0
WHILE @i < 20 
BEGIN
    SET @i = @i + 1
    insert into TABLE_CTE values (@i, CONCAT('FirstName', @i), CONCAT('LastName',  @i))
END

Sample of current query

DECLARE @PagingOffset int = 0;
DECLARE @PagingSize int = 5;
DECLARE @SelectedId int = 20;

WITH CTE_FILTERED AS (
    SELECT Id, FirstName, LastName
        --+ lot of complex subselect
    FROM TABLE_CTE
    WHERE  1 = 1
        AND FirstName LIKE '%1%'
) 
SELECT 
    --Cant explain why but looks faster than COUNT(*) OVER()
    (SELECT COUNT(*) FROM CTE_FILTERED) Total
    , CTE_FILTERED.*
FROM CTE_FILTERED
-- Could be ordered by anything
ORDER Id
OFFSET @PagingOffset ROWS FETCH NEXT @PagingSize ROWS ONLY;

Result

Total   Id  FirstName   LastName
11  1   FirstName1  LastName1
11  10  FirstName10 LastName10
11  11  FirstName11 LastName11
11  12  FirstName12 LastName12
11  13  FirstName13 LastName13

From here i see 3 solutions

1. Query the row id, then query the PagedResult (-1 row) and concact them in backend
2. Find a way to query the page number of the rowId selected then bring the user directly to this page
3. Find a way to keep our selected id on top of all results no matter the paging (filters and sorts) => best scenario in my opinion

I just achieved the solution 3 but it looks really complex, maybe there is a better way to achieve it?

DECLARE @PagingOffset int = 0;
DECLARE @PagingSize int = 5;
DECLARE @SelectedId int = 20;

WITH CTE_FILTERED2 AS (
    SELECT Id, FirstName, LastName
        --+ lot of complex subselect
    FROM TABLE_CTE
    WHERE  1 = 1
        AND (Id = @SelectedId 
            OR FirstName LIKE '%1%')
),
CTE_FILTERED_SELECTED2 AS (
    SELECT Id, FirstName, LastName
    FROM CTE_FILTERED2
    WHERE CTE_FILTERED2.Id = @SelectedId
    
    UNION ALL
    
    SELECT Id, FirstName, LastName
    FROM CTE_FILTERED2
    WHERE CTE_FILTERED2.Id != @SelectedId
    -- Could be ordered by anything
    ORDER BY Id
    -- forced to add an offset to be able to sort
    OFFSET @PagingOffset ROWS FETCH NEXT @PagingSize ROWS ONLY
)
SELECT (SELECT COUNT(*) FROM CTE_FILTERED_SELECTED2) Total
    , CTE_FILTERED_SELECTED2.*
FROM CTE_FILTERED_SELECTED2
-- forced to order by something to be able to use offset
ORDER BY (SELECT NULL)
OFFSET @PagingOffset ROWS FETCH NEXT @PagingSize ROWS ONLY;

Result

Total   Id  FirstName   LastName
12  20  FirstName20 LastName20
12  1   FirstName1  LastName1
12  10  FirstName10 LastName10
12  11  FirstName11 LastName11
12  12  FirstName12 LastName12

EDIT Final solution

SELECT TOP (@PagingSize) Priority, Id, FirstName, LastName, Total
FROM 
( 
  SELECT Priority = 1, Id, FirstName, LastName, -1 Total
   FROM dbo.TABLE_CTE WHERE Id = @SelectedId
  UNION ALL
  SELECT Priority = 2, Id, FirstName, LastName, Total
   FROM 
   (
    SELECT Id, FirstName, LastName, COUNT(*) OVER () + IIF(@SelectedId IS NULL, 0, 1) Total
      FROM dbo.TABLE_CTE
      WHERE FirstName LIKE '%1%'
      ORDER BY Id
    OFFSET @PagingOffset ROWS FETCH NEXT @PagingSize ROWS ONLY
   ) AS y
   WHERE Id != @SelectedId
) AS z
ORDER BY Priority;

Priority    Id  FirstName   LastName    Total
1   20  FirstName20 LastName20  -1
2   1   FirstName1  LastName1   12
2   10  FirstName10 LastName10  12
2   11  FirstName11 LastName11  12
2   12  FirstName12 LastName12  12

Answer 1

This seems a little simpler (but I didn't want to complicate it with retrieving counts). Basically you assign a priority to the selected row, and assign everything else a lower priority, then you can sort on that artificial column.

DECLARE @PagingOffset  int = 0,
        @PagingSize    int = 5,
        @SortId        bit = 1,
        @SortFirstName bit = 0,
        @SortLastName  bit = 0,
        @SelectedId    int = 20;

SELECT TOP (@PagingSize) Id, FirstName, LastName
FROM 
( 
  SELECT Priority = 1, Id, FirstName, LastName
   FROM dbo.TABLE_CTE WHERE Id = @SelectedId
  UNION ALL
  SELECT Priority = 2, Id, FirstName, LastName
   FROM 
   (
    SELECT Id, FirstName, LastName
      FROM dbo.TABLE_CTE
      WHERE FirstName LIKE '%1%'
      -- AND Id <> @SelectedId
      ORDER BY 
        CASE WHEN @SortId = 1        THEN Id END,
        CASE WHEN @SortFirstName = 1 THEN FirstName END,
        CASE WHEN @SortLastName = 1  THEN LastName END
        OFFSET @PagingOffset ROWS FETCH NEXT @PagingSize ROWS ONLY
   ) AS y
) AS z
ORDER BY Priority;

• Example db<>fiddle

The outer ordering matches what you wrote (SELECT NULL = don't care) but you could easily repeat the case expression on the outer:

ORDER BY Priority,
        CASE WHEN @SortId = 1        THEN Id END,
        CASE WHEN @SortFirstName = 1 THEN FirstName END,
        CASE WHEN @SortLastName = 1  THEN LastName END;

Answer 2

If you have aribtrary filters and arbitrary sorting, and want to guarantee your users always see the page with an arbitrary row in it...

DECLARE @PagingSize int = 5;
DECLARE @SelectedId int = 20;

WITH
  filtered_sorted_paged AS
(
  SELECT
    <anything>,
    (ROW_NUMBER() OVER (ORDER BY <anything>) - 1) / @PagingSize + 1  AS page_id
  FROM
    <anything>
  WHERE
    <anything>
),
  page_search AS
(
  SELECT
    *,
    MAX(CASE WHEN id = @SelectedId THEN page_id END) OVER ()  AS selected_page_id
  FROM
    filtered_sorted_paged
)
SELECT
  <stuff>
FROM
  page_search
WHERE
  page_id = selected_page_id

For large datasets repeating this for every user action seems expensive, but it appears to do what you ask...

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f3e90f195bc4e3dbcd03b4e194d94d4e