[Gllug] Perl DBI question

Ryan Cartwright ryan at crimperman.org
Tue Oct 3 16:13:54 UTC 2006


Russell Howe wrote:
> Ryan Cartwright wrote:
>> The first method has the downside that the data could change
>> between grabbing the list of IDs and doing the random select.
> 
> So grab a cursor or something?

Would that allow for one of the rows you end up selecting being removed
between grabbing the id list and the random id? Meaning you try to
select the rows with IDs 6 and 7 but 7 got deleted between grabbing the
list and selecting the random ID of 6 so it returns 6 one row.

> maxid = MAX(id);
> rnd = RANDOM(MIN(id), foo);

> SELECT * FROM
>   (SELECT id, [...] FROM table WHERE id >= rnd)
> UNION
>   (SELECT id + maxid + 1, [...] FROM table WHERE id < rnd)
            ^^^^^^^^^^^^^^
Not sure what this is actually selecting surely the second statement
tries to get a row with an id above that specified by the where (
id+maxid+1 > rnd ) - maybe I am mis-reading your pseudocode here - do
you mean that maxid is the string or the result of MAX(id)?

Also will this work if the IDs are consecutive and the ID list has no gaps?

cheers
Ryan
-- 
Gllug mailing list  -  Gllug at gllug.org.uk
http://lists.gllug.org.uk/mailman/listinfo/gllug




More information about the GLLUG mailing list