[Gllug] Perl DBI question

Russell Howe rhowe at siksai.co.uk
Wed Oct 4 09:20:05 UTC 2006


On Tue, Oct 03, 2006 at 05:13:54PM +0100, Ryan Cartwright wrote:
> 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?

Haven't tested it, but I'd expect it to.

Note that the "id + maxid + 1" bit is in the SELECT clause, not in the WHERE
clause, which means it does not determine *which* tuples are returned,
just the value of an attribute in the result set.

What the above should do is split your relation in two - those tuples whose
primary key is less than the random number you selected (let's call this
'Set A') and those tuples whose primary key is >= the random number selected
(let's call this 'Set B').

Now, you want the results ordered by primary key, starting from the
random tuple you selected, right?

So, just add an offset to the primary keys in Set A such that when you
union the two sets and order by primary key, Set A follows Set B. What
is this magic offset? MAX(primary key) + 1!

-- 
Russell Howe       | Why be just another cog in the machine,
rhowe at siksai.co.uk | when you can be the spanner in the works?
-- 
Gllug mailing list  -  Gllug at gllug.org.uk
http://lists.gllug.org.uk/mailman/listinfo/gllug




More information about the GLLUG mailing list