[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