SQL Server Window Paging Based on # of Groups

Question

Given the following table structure

Column
Id
Name
DateCreated

with the following data

id	Name	DateCreated
1	Joe	1/13/2021
2	Fred	1/13/2021
3	Bob	1/12/2021
4	Sue	1/12/2021
5	Sally	1/10/2021
6	Alex	1/9/2021

I need SQL that will page over the data based on datecreated. The query should return the top 3 records, and any record which also shares the datecreated of the top 3.

So give the data above, we should get back Joe, Fred and Bob (as the top 3 records) plus Sue since sue has the same date as Bob.

Is there something like ROW_NUMBER that increments for each row where it encounters a different value.

For some context this query is being used to generate an agenda type view, and once we select any date we want to keep all data for that date together.

EDIT I do have a solution but it smells:

;WITH CTE AS ( SELECT ROW_NUMBER() OVER(ORDER BY DateCreated DESC) RowNum,CAST(DateCreated AS DATE) DateCreated,Name
                FROM MyTable),
        PAGE AS (SELECT * 
                  FROM CTE
                  WHERE RowNum<=5)

SELECT * 
FROM Page
UNION
SELECT * 
FROM CTE 
WHERE DateCreated=(SELECT MIN(DateCreated) FROM Page)

user_1527504 · Answer

I've used a TOP 3 WITH TIES example and a ROW_NUMBER example and a CTE to return four records:

DROP TABLE IF EXISTS #tmp
GO
CREATE TABLE #tmp (
    Id          INT PRIMARY KEY,
    name        VARCHAR(20) NOT NULL,
    dateCreated DATE
)
GO

INSERT INTO #tmp VALUES
    ( 1, 'Joe', '13 Jan 2021' ),
    ( 2, 'Fred', '13 Jan 2021' ),
    ( 3, 'Bob', '12 Jan 2021' ),
    ( 4, 'Sue', '12 Jan 2021' ),
    ( 5, 'Sally', '10 Jan 2021' ),
    ( 6, 'Alex', '9 Jan 2021' )
GO

-- Gets same result
SELECT TOP 3 WITH TIES *
FROM #tmp t
ORDER BY dateCreated DESC 


;WITH cte AS (
SELECT ROW_NUMBER() OVER( ORDER BY dateCreated DESC ) rn, *
FROM #tmp
)
SELECT *
FROM #tmp t
WHERE EXISTS
(
    SELECT *
    FROM cte c
    WHERE rn <=3
      AND t.dateCreated = c.dateCreated
)

My results:

user_13979487 · Answer

As @Charlieface, we only need to replace ROW_NUMBER with DENSE_RANK. So that the ROW_NUMBER will be tied according to the same value.

When we run the query:

SELECT DENSE_RANK ()  OVER(ORDER BY DateCreated DESC) RowNum,CAST(DateCreated AS DATE) DateCreated,Name
                FROM MyTable

The result will show as follows:

So as a result, we can set RowNum<=3 in the query to get the top 3:

;WITH CTE AS ( SELECT DENSE_RANK() OVER(ORDER BY DateCreated DESC) RowNum,CAST(DateCreated AS DATE) DateCreated,Name
                FROM MyTable),
        PAGE AS (SELECT * 
                  FROM CTE
                  WHERE RowNum<=3)

SELECT * 
FROM Page
UNION
SELECT * 
FROM CTE 
WHERE DateCreated=(SELECT MIN(DateCreated) FROM Page)

The First one is as yours the second one is as above. The results of the two queries are the same.

Kindly let us know if you need more infomation.

SQL Server Window Paging Based on # of Groups

2 Answers