E
esn
Hi everyone,
This should be simple. I'm trying to pull one random record from each
group within a table. The groups are "Units" - geographic areas, and
I want to select one random point (conveniently enough, "Point") from
each Unit that satisfies certain criteria. Step 1 - query with a
random number field - Rnd([EventID]), sorted by the random number
field. EventID is an autonumber PK field. Then a query that performs
the grouping (by Unit) and pulls the first value of interest (in this
case Point) from the first record for each group. The problem is the
first query doesn't actually perform the sort correctly. Here are the
first few values in the random number field, which is set to sort in
ascending order:
RandomNumber
0.212475836277008
0.456852912902832
0.35159033536911
0.721272110939026
0.638044655323029
Clearly that's not ascending order - it doesn't appear to be any order
at all. Seems like the logic of using this setup to select a random
record is violated if the records don't actually get sorted
correctly. Every time I run the query I end up with a different
record on top, so it seems to be sorting "randomly" somehow but not by
the random number field. Any idea what's up? Is it recalculating the
random numbers after sorting the records or something?
PS - I know there are additional problems with trusting a last or
first function to do anything meaningful - that's the next hurdle but
at the moment I'd like to get the first step worked out. If anyone
has a good suggestion for returning the top 1 record within a group
(without using the first or last functions) that would help too.
This should be simple. I'm trying to pull one random record from each
group within a table. The groups are "Units" - geographic areas, and
I want to select one random point (conveniently enough, "Point") from
each Unit that satisfies certain criteria. Step 1 - query with a
random number field - Rnd([EventID]), sorted by the random number
field. EventID is an autonumber PK field. Then a query that performs
the grouping (by Unit) and pulls the first value of interest (in this
case Point) from the first record for each group. The problem is the
first query doesn't actually perform the sort correctly. Here are the
first few values in the random number field, which is set to sort in
ascending order:
RandomNumber
0.212475836277008
0.456852912902832
0.35159033536911
0.721272110939026
0.638044655323029
Clearly that's not ascending order - it doesn't appear to be any order
at all. Seems like the logic of using this setup to select a random
record is violated if the records don't actually get sorted
correctly. Every time I run the query I end up with a different
record on top, so it seems to be sorting "randomly" somehow but not by
the random number field. Any idea what's up? Is it recalculating the
random numbers after sorting the records or something?
PS - I know there are additional problems with trusting a last or
first function to do anything meaningful - that's the next hurdle but
at the moment I'd like to get the first step worked out. If anyone
has a good suggestion for returning the top 1 record within a group
(without using the first or last functions) that would help too.