Tsql "OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY" has bug

Question

in Tqsl i have order by date and if dates must be equals ,
never do not show row id = 18,

SQL1:
SELECT AtlasReportCalendarId id,StartDate,EndDate FROM TblAtlasReportCalendar ORDER BY StartDate DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

SQL2:
SELECT AtlasReportCalendarId id,StartDate,EndDate FROM TblAtlasReportCalendar ORDER BY StartDate DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

1 2021-08-10 2021-08-19
2 2021-08-20 2021-08-29
3 2021-08-30 2021-09-08
4 2021-09-09 2021-09-18
5 2021-09-19 2021-09-27
6 2021-09-28 2021-10-07
7 2021-10-19 2021-10-24
8 2021-12-15 2021-12-19
9 2022-01-04 2022-01-08
11 2022-03-19 2022-03-20
15 2022-03-21 2022-04-20
14 2022-04-03 2022-05-04
36 2022-04-21 2022-05-21
37 2022-05-22 2022-06-21
34 2022-06-01 2022-06-30
0 2022-06-04 2022-06-09
38 2022-06-05 2022-07-06
39 2022-07-07 2022-08-06
16 2022-07-10 2022-07-24
17 2022-07-30 2022-08-08
18 2022-07-30 2022-08-28
40 2022-08-07 2022-09-06
19 2022-09-17 2022-09-27
31 2022-09-22 2022-09-28
21 2022-10-09 2022-10-14
24 2022-12-08 2022-12-12
26 2022-12-25 2022-12-29
30 2022-12-30 2023-01-09
29 2023-02-01 2023-02-11
32 2023-02-22 2023-03-08

Answer 1

To avoid such situation (which is expected in your case because of sorting ambiguity), try adding the unique ID:

SELECT AtlasReportCalendarId id,StartDate,EndDate FROM TblAtlasReportCalendar ORDER BY StartDate DESC, AtlasReportCalendarId OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY  
  
SELECT AtlasReportCalendarId id,StartDate,EndDate FROM TblAtlasReportCalendar ORDER BY StartDate DESC, AtlasReportCalendarId OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY  

Answer 2

Just to clarify Viorel's answer.

Your sort order "ORDER BY StartDate DESC" does not result in a static unique order due to duplicate StartDates. Therefore, you are getting a random result for duplicate StartDates.

For windowing functions, you MUST have an order by which consistently returns the same records in the same order. In your example the simplest way to do that is to change your sort order to "ORDER BY StartDate DESC, AtlasReportCalendarId" or "ORDER BY StartDate DESC, EndDate, AtlasReportCalendarId".

Answer 3

Hi @Alireza Maleki

Or you can use "order by StartDate DESC, EndDate DESC " ,just to make the follow set of order by to be unique .