Random order in Access and SQL Server

I need to retrive a recordset in a random order, the table is going to be used for a banner display. So I want to select a random record, or at least order the recordset in a random order.

Also it can only be done with sql code, cant do it with asp or similar, and the same code should work in Access and SQL server..... :eek2:

I have been trying with something like this:

SELECT FieldID, FieldNAME
FROM Table
ORDER BY Rnd(FieldID)

FieldID would be a number/integer.

Would this acctually give me a random order, have tested some, but arent 100% sure that its "random".

Also, could one throw in some "weight" into the random?, like give one row in the table some more "possible hits"?

SQL Server doesn't do random I'm afraid (search for posts by szlamany that contain "random" to see the best option).

If Rnd in Access is the same as Rnd in VB (as most functions are), your usage of Rnd is actually incorrect, it should be: rnd() * maximum_value
(ie: the parameter is omitted).

If the numbers are actually random, your method would be ok for Access databases.

If you want this to work for multiple databases, the best option (i know it isn't one you want), is to do the randomisation at the front end.

try this
SELECT * FROM table
ORDER BY NEWID()

NewID only works in SQL Server, not access.

My usage of rnd() in access seams to be correct.

My concern is that it might not give me a correct random.

RND() in a MS SQL SELECT statement is only evaluated once in the QUERY - so you get the same value in each and every row. This is particularly poor of SQL - but that's the way it works.

Look at this thread:

http://www.vbforums.com/showthread.p...&highlight=rnd

To think about this in another way- for your banner display it doesn't matter whether the order is random, it is just important that there does not appear to be any pattern. What that means is that there can actually be a pattern, so long as its cycle is long enough that no user notices it.
You could therefore take another apporach. Supposing your table of items for banner display has 100 items. Create a table (tbl_ViewOrder or something) with 10,000 records (or 1,000,000 if you want to eliminate any chance of a user spotting a pattern), and containing the id of the item to be viewed and an autonumber field. You can create it using VB, or even manually, so that items can be strictly random or, as you said you wanted, the randomness can be weighted.
You can then select 100 records apparently at random using
SELECT TOP 100 tbl_vieworder.ID, YourTable.* FROM tbl.vieworder INNER JOIN yourtable ON YourTable.ItemID=tbl_ViewOrder.ItemID WHERE tbl_ViewOrder.ID > rnd() * 999900