|
Returning Data in Random Order with NEWID()
| For More Information... | |
For more information on this FAQ, check out the following resources:
|
When displaying a list of reviews, favorite quotes, or other pieces of information whose ordering is not vital,
oftentimes developers want to display the information in random order. That is, each visit to the page displays the
data in a potentially different order.
Displaying data in random order in a DataGrid, DataList, or Repeater is
exceptionally easy, as the "randomization" of the data can be performed entirely on the SQL-side.
In the article Using NEWID() to Randomly Sort Records, author
Bill Graziano shows how to randomize data in a SQL query in Microsoft SQL Server 7.0 and up. All you have to do is add
ORDER BY NEWID() at the end of the SQL query!
The following simple SQL query demonstrates retrieving five random records from the Articles table:
|
<![CDATA[
SELECT TOP 5 ArticleID, Title, URL
FROM Articles
ORDER BY NEWID()
]]>
|
An accompanying live demo shows the above SQL query in action.
|
Not Using Microsoft SQL Server? |
Keep in mind that the NEWID() keyword is SQL Server-specific. If you are using a different
database, you will need to use a different randomization method. There are a number of other techniques,
which are discussed in more detail at the FAQ: How can I
pick random records from a table?
|
|
DataGrid Paging and Random Records |
|
With default paging in the DataGrid, each time the user visits a new page the database query is rerun and the
results are rebinded to the DataGrid. This works great with a static ordering of data. However, if each
time the data is requested from the database it is returned in a random ordering, then obviously one cannot
page through the data in a sensical manner.
In order to page through a set of random records, it is imperative that the random record ordering
be saved somehow, and persisted across postbacks. This can be accomplished in a number of ways, one way being
filling a DataSet and caching this DataSet in Session state.
|
| 
