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.








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