August 18, 2009

Selecting Random Records from a Table

How do you go about selecting N random records from a database? You can always write code for this purpose but that code will be complicated, long and slow-performer. You can instead write a query and let the database handle all the logic for you.

SQL Server/T-SQL Query for Selecting Random Records

The following T-SQL query will return 10 random records from the Products table:

SELECT TOP 10 * FROM Products ORDER BY NEWID( )

The query relies on the NEWID function that generates a temporary GUID (globally unique identifier) for each row. This GUID is a unique, randomly generated number as demonstrated below:

SELECT Products.*, NEWID( ) FROM Products
ProductID   ProductName
----------- --------------------------------- ------------------------------------
1           Chai                              65A1D326-12C6-4353-B93F-7BCD51AB4F84
2           Chang                             09172785-0819-4728-82FF-66F15F113F47
3           Aniseed Syrup                     F34E3D71-2103-4B4C-AA5A-8C9867CFFA93
4           Chef Anton's Cajun Seasoning      FC152675-C2E9-44B1-BD90-2FB28A77AC72
5           Chef Anton's Gumbo Mix            355DB4F8-CE09-4CC5-9038-FE040B1ED3D4
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
73          Röd Kaviar                        D9FEF333-1089-4BFE-9A46-53B3D1FD0D9F
74          Longlife Tofu                     6F007528-3A90-43CF-8621-768064086D34
75          Rhönbräu Klosterbier              FB6F597E-9547-439A-885D-77D0999BE28C
76          Lakkalikööri                      17042A7E-EF71-4142-B235-836AC42D484C
77          Original Frankfurter grüne Soße   D81327A0-0F17-4BC5-B1D8-D67BA55CF220

Using NEWID function in the ORDER BY clause will return the rows in random order. You can then select desired number of rows from the top:

SELECT TOP 10 * FROM Products ORDER BY NEWID( )
ProductID   ProductName
----------- ---------------------------------
29          Thüringer Rostbratwurst
64          Wimmers gute Semmelknödel
45          Røgede sild
58          Escargots de Bourgogne
24          Guaraná Fantástica
21          Sir Rodney's Scones
31          Gorgonzola Telino
10          Ikura
54          Tourtière
46          Spegesild

MySQL Query for Selecting Random Records

The following MySQL query will return 10 random records from the Banners table:

SELECT * FROM Banners ORDER BY RAND( ) LIMIT 0, 10

This behavior of the query is same as that of the query shown above, except that we are using MySQL RAND function and LIMIT clause. Example output of the query looks something like this:

SELECT * FROM Banners ORDER BY RAND( ) LIMIT 0, 10
BannerID   Image               Caption
---------- ------------------- -------------------
2208       banner_e66391.gif   Cell Phones
5831       banner_5be388.jpg   Cricket News
21         banner_f36a6f.gif   Free Sims
6309       banner_3ce6e8.jpg   SQL Server
1067       banner_273446.jpg   PHP Training
926        banner_8f1ebf.gif   MySQL Maintenance
5130       banner_4a0702.gif   HTML/CSS Editor
4459       banner_47fec5.jpg   Visual Studio
3847       banner_797d8a.jpg   Apache Web Server
4894       banner_4a0702.gif   Web Hosting

Notes

  • The behavior of T-SQL RAND function is different from the MySQL RAND function, therefore it is not used in the T-SQL example.
  • There is a performance penalty associated with the above-mentioned queries, therefore they are best suited for small/medium size tables.