[thelist] SQL select help

Luther, Ron Ron.Luther at compaq.com
Fri Jul 27 08:46:13 CDT 2001


Hi Guy,


Neat question - and one I plan on working on in my infamous "free time".
(HA!)

Anyway, I'm looking at it as two cases:

Case One: If you're working with "reasonably small" recordsets, you can
always 'weenie out' and just do the random selection in the server side
language of your choice.  [It looks to me like most server-side languages
give you a 'RecordCount' or 'num_rows' object to use as an upper bound - and
they all have some kind of 'random' function.]  Grab the full "select *"
recordset.  Set a 'magic record' variable to the random selection.  Loop
through the recordset and output only that record.

Case Two: Big recordsets.  If you're looking at returning one record, (or a
handful), out of  eight million records, then I think I'd recommend doing
some 'engineering' work on the backend tables.... so your users don't "time
out".  There are a number of ways to do this.  Here's one:  Maybe create a
small "counter" table that has 'category' and 'number of record fields', and
a "count_index_by_category" table that has 'primary_key_from_the_REAL_table'
and a 'sequential _numeric_counter' field.  Now you can hit the "counter"
table to find the upper bound for your random function.  Run the random
function.  Close that recordset and open a second to select your record by
matching the random number to the 'sequential_index' to pick off the
'primary_key' to match into the real table to get your data.  ... Tinkers to
Evans to Chance ... but you get the idea.


HTH,


RonL.


-----Original Message-----
From: Guy Babbitt [mailto:Guy_Babbitt at FSAFOOD.COM]
Subject: [thelist] SQL select help

I then need to return one single random record from that result set for use.




More information about the thelist mailing list