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.