Postgres Pro
Downloads
Home   >   mailing lists

Thread: Query with LIMIT but as random result set?

Query with LIMIT but as random result set?

From
Stefan Keller
Date:
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).

Yours, Stefan

Re: Query with LIMIT but as random result set?

From
Adrian Klaver
Date:
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

Re: [postgis-users] Query with LIMIT but as random result set?

From
Stefan Keller
Date:
Hi,

Thanks a lot to Adrian, Nicolas and Brooks.
"... ORDER BY random() LIMIT 10;" works ok.

But with the following option it gets more tricky assume:
> And as an option the (limited) resultset should be spatially
> distributed (not clustered).

I'm thinking about some radial spatial distribution function.

Yours, Stefan


2013/1/8 Brooks Kehler <brookskehler@gmail.com>:
> this should work -
>
> order by random() limit 10;
>
>
>
> On Tue, Jan 8, 2013 at 10:20 AM, Stefan Keller <sfkeller@gmail.com> 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).
>>
>> Yours, Stefan
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>