CODEWITHSUNDEEP
sqlsql-serverrandomsql-order-by
Mike Flynn
Mike Flynn

Reputation: 24325

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

Upvotes: 2

Views: 570

Answers (1)

sticky bit
sticky bit

Reputation: 37472

In the following I will use the table sortme defined as:

CREATE TABLE sortme
             (id integer
                 IDENTITY (1, 1),
              PRIMARY KEY(id));

Using ORDER BY rand(<seed>) with <seed> derived from the current day might be the first approach that comes into mind.

DECLARE @today datetime = '2021-01-01 01:01:01';

SELECT *
       FROM sortme
       ORDER BY rand(convert(integer,
                             convert(varchar(8),
                                     @today,
                                     112)));

rand() guarantees that the value generated is the same, if the <seed> is the same. So we got this one point checked. Sort of... because the value will also be the same for every row. So using rand(<seed>) for a fixed <seed> will effectively do nothing.

db<>fiddle

We can try to overcome this by binding the random value to a key of the result. The random value will be stable for day and key, i.e. the row the key identifies, for a day.

    DECLARE @today datetime = '2021-01-01 01:01:01';
    SELECT *
           FROM sortme
           ORDER BY rand(id * 100000000
                         + 
                         convert(integer,
                                 convert(varchar(8),
                                         @today,
                                         112)));

But unfortunately, every (simple) formula to combine the key and the day I came up with did produce random values close enough for every day, so that the order ways the same every day. It didn't scatter well.

db<>fiddle

Now if we think about it, we want a function that scatters even monotonically increasing values well. And that is the domain of hash functions.

So we can use hashbytes().

DECLARE @today datetime = '2021-01-01 01:01:01';

SELECT *
       FROM sortme
       ORDER BY hashbytes('SHA2_512',
                          concat(convert(varchar(8),
                                         @today,
                                         112),
                                 id));

The hash will be stable throughout the day and the key for a row as it depends only on these values. And the fact that (certain) hash functions scatter well, makes the result "look random".

db<>fiddle

Deleting from or inserting into the table will of course result in rows appear or disappear somewhere in between other "old" or remaining rows. But if the key we use is stable, say like an auto increment that doesn't fill gaps, the order of the "old" or remaining rows among themselves will be stable throughout the day anyway.

Of course, be aware that this isn't random in terms of being unpredictable!

But I assume that its purpose is just about improving the user experience, presenting a result that "feels" random each day to make the content more interesting and exciting. It should be good enough for that.

Upvotes: 4

Related Questions