Postgresql Random Rec Set - no Duplicates with Livecode

[Peter Haworth]

I'm not a Postgresql user but how about something like this.  This assumes you have a numeric primary key in the table you want to access.

1. Use the max() function to get the max value for the primary key of the table 

2. Generate 21 random numbers between 1 and the max primary key, ensuirng you don;t get the same random number more than once

3. SELECT  * FROM table WHERE <primarykey> IN (randomnumberlist)

You may not get 21 rows because there may not be an entry for every primary key in your list.  I guess you'd have to check how many you got back, then go through the process again for however many rows you are missing, etc.  Or you could generate one random number, try to get an entry, if it exists add it to you LC storage, if not generate another random number and keep going until you have 21 rows..

Pretty cumbersome but I think that would work.

Pete Haworth

On Feb 2, 2011, at 2:40 PM, Rick Harrison wrote:

> Hi Pierre,
> 
> What I'm looking to do is to perform the Random pick from the database,
> not reorder my results randomly after I've gotten all records.  I don't think
> the code links below help me to do that.  I've been down this road already.
> 
> Thanks anyways!
> 
> Rick
> 
> On Feb 2, 2011, at 4:29 PM, Pierre Sahores wrote:
> 
>> It's a SQL syntax task, not a revDB one and as soon as the query will be OK, revDB will just handle it as expected.
>> 
>> <http://archives.postgresql.org/pgsql-general/2007-08/msg02034.php>
>> <http://bytes.com/topic/postgresql/answers/857541-random-sample-rows>
>> 
>> Best regards,
>> 
>> Le 2 févr. 2011 à 21:29, Rick Harrison a écrit :
>> 
>>> Hi there,
>>> 
>>> I need to know the best way to get a random subset
>>> of records without duplicates in the resulting record
>>> set using a Postgresql database query with Livecode
>>> on On-Rev.
>>> 
>>> In other words, I don't want to have to get all of the
>>> records in the database first just to be able to pick
>>> out the 21 random records i need, as that seems rather
>>> inefficient.  Assume a couple of thousand records
>>> in the database or more.
>>> 
>>> Thanks for your ideas in advance!
>>> 
>>> Rick
>>> 
>>> _______________________________________________
>>> use-livecode mailing list
>>> use-livecode at lists.runrev.com
>>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>> 
>> 
>> --
>> Pierre Sahores
>> mobile : (33) 6 03 95 77 70
>> 
>> www.woooooooords.com
>> www.sahores-conseil.com
>> 
>> 
>> 
>> 
>> 
>> _______________________________________________
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
> 
> __________________________________
> Rick Harrison
> 
> You can buy my $10 music album "Funny Time Machine" digital CD on the iTunes Store Now!
> 
> To visit the iTunes Store now to listen to samples of my CD please click on the
> following link.  (Please note you must have iTunes installed on your computer for this link to work.)  
> 
> http://phobos.apple.com/WebObjects/MZStore.woa/wa/viewAlbum?playListId=213668290
> 
> 
> 
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>