Daily Archives: March 7, 2010

Query to Select a Random Record from a Table in SQL Server

Here is a quick and easy to way to select a random records from a table using T-SQL.
 
Create a Temp Table using the following query:
 

CREATE TABLE #TempTable (
IntValue INT NOT NULL,
StrValue NVARCHAR(20) NOT NULL)
GO
Now insert some sample data into the Temp Table using the following query:

INSERT INTO #TempTable (IntValue,StrValue)
SELECT IntValue, StrValue
FROM (SELECT 1 AS IntValue, ‘String Value 1’ AS StrValue
UNION ALL
SELECT 2 AS IntValue, ‘String Value 2’ AS StrValue
UNION ALL
SELECT 3 AS IntValue, ‘String Value 3’ AS StrValue
UNION ALL
SELECT 4 AS IntValue, ‘String Value 4’ AS StrValue
UNION ALL
SELECT 5 AS IntValue, ‘String Value 5’ AS StrValue
UNION ALL
SELECT 6 AS IntValue, ‘String Value 6’ AS StrValue
UNION ALL
SELECT 7 AS IntValue, ‘String Value 7’ AS StrValue
UNION ALL
SELECT 8 AS IntValue, ‘String Value 8’ AS StrValue
UNION ALL
SELECT 9 AS IntValue, ‘String Value 9’ AS StrValue
UNION ALL
SELECT 10 AS IntValue, ‘String Value 10’ AS StrValue) StaticData
GO

 
Now run the following queries to see how 2 RANDOM rows are selected from Temp Table every time you run these queries.

SELECT TOP 2 *
FROM #TempTable
ORDER BY NEWID()
GO

SELECT TOP 2 *
FROM #TempTable
ORDER BY NEWID()
GO

Here is the output of the above queries. Run these queries a couple times to see the difference in the number of selected rows every time the above queries are run.
 

 

Advertisements