Re: select where id=random()*something returns two results

On Sun, 2003-09-21 at 08:21, Ulrich Meis wrote:
> > -----Original Message-----
> > From: pgsql-bugs-owner(at)postgresql(dot)org [mailto:pgsql-bugs-
> > owner(at)postgresql(dot)org] On Behalf Of Jean-Luc Lachance
> > Sent: Friday, September 19, 2003 4:44 PM
> > To: Rod Taylor
> > Cc: Ulrich Meis; pgsql-bugs(at)postgresql(dot)org
> > Subject: Re: [BUGS] select where id=random()*something returns two
> results
> >
> > Rod,
> >
> > If the table has 100,000 tupples your query is generating 100,000 new
> > tupples...
> > Try:
> >
> > select * from quotes where id = (
> > select int8( 1 + random() * (
> > select id from quotes order by id desc limit 1)));
> >
>
> How about
>
> select * from quotes where id=1+int8((select random())*(select max(id)
> from quotes));
>
> It works, but is it more or less efficient?

Run EXPLAIN ANALYZE on them both and you tell me which is more
efficient.

Efficiency of a query tends to change with the data that it is being
executed on.