Select top n records (randomized)

[Guest]

I use a query like this:
SELECT TOP n *
FROM db
ORDER BY RND(Autonumber_Field)
Is it possible to get the value for "n" from a table where it was introduced
before?
This is necessary because the number n is sometimes, not very often, changed.

Thank you in advance.

 

[Allen Browne]

Try:
SELECT TOP n *, RND(Autonumber_Field)
FROM db
ORDER BY RND(Autonumber_Field)

While I can't guarantee that this will give you the right results, I would
be very surprised if the query optimizer called the function twice for the
same value. It generally assumes that the function will return the same
result if the arguments are the same, and so doesn't call it again if
nothing changed.

 

[Guest]

Thank a lot, Mr Allen, but it seems that Access doesn't understand what is n.
I think this is normally because n is not defined at the moment when the
query runs. I assume that n already exists in another database than the
mentioned "db".
- Mani -

 

[Allen Browne]

You will need to hard code the number you want (e.g. 5) in place of n in the
query statement.