Pagination order by date ignores some data having same date

Question

I have multiple rows of data in an unsorted way (IDs are not showing any certain order). I want to display them ordered by date descending (mdate column). I use this query to load next page where the min date is extracted from the last row of displayed data (by JS).

select top 100 * 
from production 
where mdate < '2023/10/03' 
order by mdate desc

When I use mdate < '2023/10/03' obviously if there are any data remaining from 2023/10/03 which were not displayed before, will be ignored and if I use mdate <= '2023/10/03' some data may repeat.

How can I manage that?

Answer 1

Do not rely on mdate column only. Interact with ID column as well and complete your logic by a combination of mdate and ID:

select top 100 * from production where 
(mdate < '2023/10/03' 
  or 
     /* Remaining data from 2023/10/03 */
     (mdate='2023/10/03' 
     and
     /* sample ID to be extracted from last displayed data as well as did for max date */
     id<'21344') 
) 
order by mdate desc,id desc"

In the logic above, I added id desc for order also I tried to extract remaining data from 2023/10/03 by comparing against a max ID (21344 as an example extracted from previously displayed data) and combined those data with any other data having mdate < '2023/10/03'