D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.



My alternative to scraps of paper lying about.



Others may find bits of it useful!








Wednesday, 9 March 2011

Return a random row - SQL

Instead of using the RAND() function which I believe is not truly random since using the same seed value results in the same output:



Whereas, CHECKSUM(NEWID()) would work:


and can thus be used to return a random row:


SELECT TOP(1) School.SchoolNameFROM School
ORDER BY CHECKSUM(NEWID());

2 comments:

Anonymous said...

the syntax
"select top 1 school.schoolname
from school
order by newid()"
(without quotes) is enough

Dom Horton said...

are you sure?

http://www.kodyaz.com/articles/article.aspx?articleid=21

and similarly evidenced by Itzik Ben-Gan in his T-SQL querying book