Re: Query with LIMIT but as random result set? - Mailing list pgsql-general

On 01/08/2013 07:20 AM, Stefan Keller wrote:
> Hi
>
> I have a query like this
>
>    SELECT ST_AsText(way) geom, name AS label
>    FROM osm_point
>    LIMIT 10;
>
> When I repeatedly do this, the result set will be always the same.
> I have observed this only empirically and I know that the ordering of
> the result set is undefined without ORDER BY.
> There are two indexes involved, one geospatial for way and one for name.
>
> My question is: Does someone have an idea on how to randomize the
> result set on every consecutive query?
> And as an option the (limited) resultset should be spatially
> distributed (not clustered).


SELECT ST_AsText(way) geom, name AS label
     FROM osm_point ORDER BY random()
     LIMIT 10;

>
> Yours, Stefan
>
>


--
Adrian Klaver
adrian.klaver@gmail.com