Random records..

I was looking for a way to select random records from a SQL database and come across this little beauty..

Code: Select all

SELECT
    TOP 5 *
FROM
    table
ORDER BY
    NewID()

(*) not a good idea to use *, used for the example. Always use only the fields you require.

This basically selects 5 random records from a table which you can then iterate through, bung in a recordset, etc...

This was done on MsSql, and should work on MySql and Access.

Just sharing...

u2o

Hi U2.... yeah, I use that a lot.

Doesn't work in access though to my knowledge.

Actually.... I'm gonna add this to the Articles Area.

__________________
Click the 'Thanks!' button if this post has helped you

Rob - Webforumz Founder

tried it in Access, get the error:

Undefined function 'NewID()' in expression

Im sure theres an easy way to do this in Access tho.

Not that easily there isn't.

Could use getRows and then output an array element based on a random number. But I don't think you can do it in a SQL statement

Or maybe get your recordset and then objRS.Move to a record between 1 and recordcount.

Yeah, sure you can.. I did it in an article for Rob on this very subject. I am not quite sure when the article is going to be posted, see Rob...

u2o

THe article has been added to the portal (Version 2)

Smokie... as you are an admin, you already hae access to this. Check it out.

__________________
Click the 'Thanks!' button if this post has helped you

Rob - Webforumz Founder

nice one