Dan Bolser <dmb@stripped> wrote on 08/25/2005 08:03:23 AM:
> Hi, I think I saw this question being asked before, but I can't remember
> if their was a simple answer.
> If I have a table of two columns (PK and FK) with a one to many
> relationship, i.e.
> PK FK
> 1 a
> 2 a
> 3 a
> 4 b
> 5 b
> 6 b
> 7 c
> 8 c
> 9 d
> Can I easily select a truly random value of PK for each distinct value
> of FK?
> I use the words 'truly random' as I think the term 'pseudo random'
> applies to the following syntax...
> SELECT PK,FK FROM above_table GROUP BY FK;
> Of course I can do...
> SELECT MIN(PK),FK FROM above_table GROUP BY FK;
> SELECT MAX(PK),FK FROM above_table GROUP BY FK;
> But what I would really love to do is something like...
> SELECT RANDOM(PK),FK FROM above_table GROUP BY FK;
> Any general (and simple) SQL solution to this problem?
> Any hacker out there who can put together a UDF in 10 minutes flat?
> The problem is slightly more complex than stated, because PK is actually
> a multi-part primary key. In my fevered imagination I picture syntax
> that looks like this...
> SELECT RANDOM(ROW(PK_P1,PK_P2)),FK FROM above_table GROUP BY FK;
> but that could be a garbage suggestion.
> Any pointers to previous answers to similar questions would be great.
> All the best,
I can think of a way to do it in three steps. The last time I saw this
type of question, the poster was looking for ways of random sampling
within a population.If you application is similar, this approach will
probably meet your needs. If this is meant to produce random output (as in
a constantly shifting web page) it may not scale well for you.
Create a temporary table containing your PK fields and your FK field(s).
When you populate that table create an extra column of random numbers.
Create another temporary table to hold your PK and FK values but this time
apply a UNIQUE index on the FK field. Do an INSERT IGNORE from your first
temp table into this second ORDERed BY the random value. That should give
you a pseudo-random list of PK values, one per each FK with each FK
appearing only once in the list. The last step is to JOIN this final temp
table with your FK source and your original data to produce the random
list of matching items.
As I cautioned earlier, this probably will not scale well for frequent
use. I am sure that there are other ways to achieve the same effect but I
haven't had my coffee yet today and that's as good as I got... ;-)
Unimin Corporation - Spruce Pine