dimanche 21 mars 2021

Random order set to certain date

Is there a way to do a random order on a listing of events and have it stay that way only for the day? There is paging involved so caching isn't really a good option.

I know I can do random ordering by NEWID(), but that would show a different order on page 100 if it was never cached and contain the same items on page 1. If it was by date, then it will show the ordering, no matter the page cache, and reset the next day.

    WITH PagedResults AS 
        (
            SELECT CASE WHEN @SortOrder = 'Name' AND @SortDirection = 'DESC' THEN ROW_NUMBER() OVER (ORDER BY ep.Name DESC, ev.Id ASC)
            ELSE ROW_NUMBER() OVER (ORDER BY ev.Id ASC) END  AS [Row],
            CASE WHEN @SortOrder = 'Name' AND @SortDirection = 'DESC' THEN ROW_NUMBER() OVER (ORDER BY ep.Name ASC, ev.Id DESC)
            ELSE ROW_NUMBER() OVER (ORDER BY ev.Id DESC)
            END  AS [RowReverse],
                ev.Id,
                ep.Name,
    )
        SELECT 
                [Row] + RowReverse - 1 AS Total, 
                [Row],
                [RowReverse],
                Id, 
                Name,
        FROM PagedResults
        WHERE [Row] BETWEEN (((@Page - 1) * @PageSize) + 1) AND (@Page * @PageSize)
        ORDER BY [Row], [RowReverse] DESC



Aucun commentaire:

Enregistrer un commentaire