By Brian, 8 months and 23 days ago

Rand() isn't Random

If you use Microsoft SQL 2000, you've probably at one point had to pull random results. The built in rand() function simply doesn't work. It gives you a random value the first time, and every time after it is the same it seems.

I found a new way to do this today after 20 pages of searching, so hopefully this will help someone else.

ORDER BY newid()

yup, it's that simple.

  1. SELECT
  2.   TOP 2
  3.     field1
  4.    ,field2
  5. FROM
  6.   TABLE
  7. WHERE
  8.   field1 = 1
  9. ORDER BY
  10.   newid()

To be noted, this will put a full table scan in place before it selects the top 2, so keep performance in mind.

No comments

Be the first to write a comment on this post.

Write a comment

If you want to add your comment on this post, simply fill out the next form:





* Required fields

You can use these XHTML tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>.

No trackbacks

To notify a mention on this post in your blog, enable automated notification (Options > Discussion in WordPress) or specify this trackback url: http://​skeymedia.com/​rand-isn-t-random/​trackback/