Get Randomly ordered records with pagination

Neo Likotsi
Jan 9, 2020 / Neo Likotsi

Using MSSQL as a database engine to get a randomly ordered results to display you would use:

Select [column_name] from table_name order by NEWID();

This statement is a quick solution to get random records. The problem for pagination is that you want to preserve the randomness of the records while paging so as not to get repeat results. the solution is to use

Select [column_name], CHECKSUM(RAND([column_name] + $control_key)) as 'rnd' 
from table_name order by rnd;

The $control_key is variable you pass in to the RAND function to invent some unique key.

see documentation for CHECKSUM and RAND