Random result from query

Recently, I was upgrading some code and we needed to pull a random result from a table. Not thinking about the SQL server version I went ahead and created a query using common table expression (CTE).

It looked like this (the real query has proper column and table names!)

WITH cte AS
(
    SELECT TOP 1 id FROM table ORDER BY NEWID()
)
SELECT
	table.id,
	table.id2,
	table.column1,
	table.column2,
	table.column3,
	table.column4,
	table2.column1
FROM
	cte
INNER JOIN
	table
ON
	table.id = cte.id
INNER JOIN
	table2
ON
	table2.id = table.id2

Super, I thought. It works great on our development server and boy is the code a lot simpler than before.

I moved it live and it didn’t work. We had SQL 2000 on live and CTE wasn’t introduced until 2005…

…So back to the old code. But surely I could improve on the old code and looping through result. Yes, I could :)


<cfquery datasource="datasource" name="qryRange">
	SELECT
		id
	FROM
		table
</cfquery>

<cfset listIds = valueList(qryRange.id) />
<cfset randNum = RandRange(1, listLen(listIds)) />
<cfset selectID = listGetAt(listIds,randNum) />

<cfquery datasource="datasource" name="returnQuery">
	SELECT
		table.id,
		table.id2,
		table.column1,
		table.column2,
		table.column3,
		table.column4,
		table2.column1
	FROM
		table
	INNER JOIN
		table2
	ON
		table2.id = table.id2
	WHERE
		table.id = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#selectID#" />
</cfquery>

The main areas that I improved was that before, it was just using the record count of the number of records returned from the query and getting a random number from 1 to the record count and looping until it go to that ID.

Problems happen if something has been deleted from the table and there isn’t an ID for the random number. Also, it wouldn’t ever select a result if the ID was a number larger than the number of records in the table.

Once we upgrade to a newer SQL I’ll use the better code, but for now this is a vast improvement.

Hope this helps someone.

1 thought on “Random result from query

  1. If you are looking to pull a random record from a table why do you need a CTE? Wouldn’t an ORDER BY NEWID() give you that? NEWID is available in SQL 2000 and up.

Comments are closed.