Re: Using SQL to return random rows to an Array, and then randomly select items from that array

abking@my-dejanews.com wrote: > > I am trying to use SQL to randomly pull entries from a table, having the > fields image_id, image_name, town_id, etc... and then place the image_name > field associated with a certain town_id into an array of string values (the > image name, in Visual Basic Script, or Visual Basic), then determine the size > of the array (how many elements), and randomly select an image name for > display from that array using the size as a random seed. Does anyone know > how to do this? Thanks! > > -----== Posted via Deja News, The Leader in Internet Discussion ==----- > http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum You need a random number generating function. But what numbers should it generate? You could use rowids or a numeric primary key. Then you have the problem of keeping track of misses and double hits. How about SELECTing the primary keys and INSERTing them into a table with one other column: a random number. You then UPDATE this random number column (or do it with a stored procedure during SELECT/INSERT). You then sort by the random number and SELECT the first however many rows you want, one in your case. This might not be too efficient because you have to generate all the random numbers each time. However, if you really want to select each row only once, it is worth considering. If you could guarantee that the original table had a primary key or some other unique column with a contiguous sequence of numbers from (say) 1 to N, then it would be fairly easy to generate a uniformly distributed random number in this range using Visual Basic and then use it to fetch the row. If your sequence had gaps you would "miss" and would have to try again. The more gaps, the more misses. The precise solution depends on exactly how you want to select your random items. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 --- If all else fails, read the instructions. All opinions are my own and not those of Bayer plc. My Internet plumbing does not allow me to mail and post news together. Sorry. --- Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/