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@postgresql.org [mailto:pgsql-bugs-
> > owner@postgresql.org] On Behalf Of Jean-Luc Lachance
> > Sent: Friday, September 19, 2003 4:44 PM
> > To: Rod Taylor
> > Cc: Ulrich Meis; pgsql-bugs@postgresql.org
> > Subject: Re: [BUGS] select where id=3Drandom()*something returns two
> results
> >=20
> > Rod,
> >=20
> > If the table has 100,000 tupples your query is generating 100,000 new
> > tupples...
> > Try:
> >=20
> > select * from quotes where id =3D (
> >   select int8( 1 + random() * (
> >     select id from quotes order by id desc limit 1)));
> >=20
>=20
> How about
>=20
> select * from quotes where id=3D1+int8((select random())*(select max(id)
> from quotes));
>=20
> 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.