Just in Chronicles

Life as a Voyage

Retrieving Randomly Sorted Data from MS-SQL

Reference: http://www.sqlteam.com/article/using-newid-to-randomly-sort-records

Data is often retrieved in random order when necessary. It’s possible within ASP.NET pages using VB or C#. However, if MS-SQL provides a method or keyword that sorts data in random order, why not using the one instead?

NEWID() function is the one that brings about convenience for random sort order.

   1: SELECT

   2:     *

   3:   FROM

   4:     [TableName]

   5:  ORDER BY

   6:     NEWID()

The query above returns a data set in random order using NEWID() in ORDER BY clause.

The query below returns only one record selected in random order by using NEWID() in ORDER BY clause.

   1: SELECT

   2:     TOP 1

   3:   FROM

   4:     [TableName]

   5:  ORDER BY

   6:     NEWID()

NEWID() creates a number randomly created by the MS-SQL engine. Hence, by using this number we can randomise data.

Well, it’s easy, isn’t it? 🙂

Advertisements

Written by Justin Yoo

23/05/2009 at 06:33