Sunday, July 27, 2008

How to shuffle results - SqlServer?

This is an interesting question asked by Mr. Ram Nath Rao.
The history is:

Mr. Nath wants to shuffle his record(s) every time when the page refreshes, the same has been tried with the use of rand() function by him. Unfortunately, the results were not as expected.

Now, lets elaborate some of interesting points towards this :

When anybody use rand() function what happened [check followings)]:

Select rand()as Random Number --creates random number

The result may be:

When you will repeat above statement, the new result is entirely different from the earlier one.

Now, try another similar query:

Select rand()as Random_Number,* from Employees

The result may be:

In above, result note-down first column Random_Number, this column has the similar value through-out the result.

In another words from above, we can sum-it up that the rand() function, generates a random number which is a new every time we press or execute the query.

Also, it doesn't change with rows when result-set retrieves more than one row(s). So, the problem of Mr. Ram Nath Rao doesn't resolve with the use of rand() function.

I recommended newid() to retrieve the solution of Mr. Ram's problem.

Check the following query:

Select newid()as RowId,* from Employees

Above generates following result(s):-

Note-down first column of above result(s), every row has a new value.

Now, lets try to ad-more stuff in above:

Select newid()as RowId,* from Employees order by newid()

Now, regenerate above result(s) one more time, you can get different resul(s). This is the solution of the problem.

The above is a short-description how we can get random data in SqlServer2000.

Its time to do all above at application-level, I have decided to use Vs2005:

Step(s) to use:

1. Start your Vs2005
2. Create a New Website project named its as 'Shuffle Result'.
3. Rename your default web-page to 'shuffleresults.aspx'
4. Write the following lines

5. Press F7 or choose code-view from Solution-Explorer
6. Add following sort-of-code in 'shuffleresults.aspx.cs'

7. Run the above application by pressing F5.
8. It will generate following result(s):

This is the normal output.

9. Click on 'Shuffle Results' button and check the output it might be s following :

The above is described "How one can shuffle the result-sets".


  1. I want some articles on WCF web services and to how to debug a webservice.

  2. Thanks Ali!

    Definitely I will post some stuff regarding same in coming couple of days.