If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Random Function for Selecting Records
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. |
Thread Tools | |
Display Modes | |
|
|