Dan Bolser <[EMAIL PROTECTED]> 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, > Dan. > > 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... ;-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
