Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Returning Rows in Random Order - Part II - Revenge of the Randomizer
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 09/01/2000 :  17:25:23  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Wow, it looks like the>Returning Rows in Random Order article is one of our most popular ever!

If you liked the first article, then read on; I received a number of very good follow up questions from Nick that you might find interesting. This article focuses on the performance of the solution I proposed in the first article, as well as the problems associated with returning a single randomly chosen row from a table.

Article Link.

Starting Member

1 Posts

Posted - 10/30/2001 :  08:48:44  Show Profile  Reply with Quote
I have been using the random ordering of rows and it works very well.

I have now come accross a problem. I am using ASP to create a recordset and want to page through it a specified number of records at a time.

I need a bit of guidance if possible, is there any way I can generate the #temp table and store it while the user pages through the records.

I can pass in a parameter that indicates whether to generate a new random order or to page through the existing dataset.

Thanks in advance for any help.

Go to Top of Page

Starting Member

4 Posts

Posted - 12/19/2001 :  10:17:56  Show Profile  Visit rdwilliamsjr's Homepage  Reply with Quote
-- Try this code to generate a random number
-- Calculate seed value
-- The seed is based on a GUID created by the NEWID function.
SELECT @seed = CONVERT(int, CONVERT(varbinary, REPLACE(CONVERT(varchar(50), NEWID()), '-', '')))

-- Use seed value in RAND() function to create a random number between specified minimum and maximum range.
SELECT @random_number = CONVERT(int, (1 + (@max_range - @min_range + 1) * RAND(@seed)))

Go to Top of Page

Starting Member

1 Posts

Posted - 03/27/2003 :  13:06:45  Show Profile  Visit Robbo's Homepage  Reply with Quote
Try this - it uses the mod operator (%) based on number of milliseconds. Bit messy - but I've got some more testing before it's pukka.

SELECT CAST((rand(24701) * id) * 24701 AS int) % DATEPART(ms, GetDate() ) + DATEPART(ms, GetDate() ) AS random_order

Change to SELECT TOP 1 for single row.

I add the id of the row, but perhaps this isn't necessary?

The 24701 comes from the calling code and was genearted by another Random function which works a bit better than the sql Fn. Seems to work without this changing - but best to be safe...

- Robbo (one day I will finish my own website!)

Go to Top of Page

Starting Member

1 Posts

Posted - 05/21/2003 :  11:51:21  Show Profile  Visit asiemer's Homepage  Reply with Quote
I use variations of this stored proc pretty much daily for paging through records. It is as fast as can be and very easy to work with. The ASP that you will need to use it is posted below.

@Page int,
@RecsPerPage int

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON

--Create a temporary table
Name varchar(50),
Price currency

-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (Name, Price)
SELECT Name,Price FROM tblItem ORDER BY Price

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
MoreRecords =
FROM #TempItems TI
WHERE TI.ID >= @LastRec
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec

-- Turn NOCOUNT back OFF

-- And here is the ASP --

<!-- This assumes you already have a recordset object
explicitly created name objRS -->

'How many records per page do we want to show?
Const iRecordsPerPage = 10

Dim currentPage 'what page are we on??
Dim bolLastPage 'are we on the last page?

if len(Request.QueryString("page")) = 0 then
currentPage = 1
currentPage = CInt(Request.QueryString("page"))
end if

'Show the paged results
strSQL = "sp_PagedItems " & currentPage & "," & iRecordsPerPage
objRS.Open strSQL, objConn

'See if we're on the last page
if Not objRS.EOF then
if CInt(objRS("MoreRecords")) > 0 then
bolLastPage = False
bolLastPage = True
end if
end if

List of Items
Do While Not objRS.EOF %>
<% objRS.MoveNext
Loop %>
'Only show the previous button if we are NOT on the first page
if currentPage > 1 then %>
<INPUT TYPE=BUTTON VALUE="<< Previous <%=iMaxRecords%> Records"
<% end if

'Only show the next button if we are NOT on the last page
if Not bolLastPage then %>
<INPUT TYPE=BUTTON VALUE="Next <%=iMaxRecords%> Records >>"
<% end if %>

<div align="center">Andrew Siemer |
.:: Changing the world one keystroke at a time ::.</div>
Go to Top of Page

Starting Member

2 Posts

Posted - 12/09/2003 :  12:03:31  Show Profile  Reply with Quote
You can also use the Sine-function to generate a nearly-random sequence:

"select top 10 * from myTable order by Sin(UnitID * Rand() * 1000)"

With UnitID being a unique value for each record. The 1000 ensures that the numbers are not within the same 2pi-period.
This will also work for older versions of sql-server.

Channing Jones
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000