Re: limitby without orderby

Hi,

On 22 September 2011 21:32, Rohan Malhotra <yourbuddyrohan@gmail.com> wrote:
> Hi Gurus,
> What is difference between
> select * from items order by random() limit 5;
> and
> select * items limit 5;
> my basic requirement is to get random rows from a table, my where clause

This one says: give me first five rows which you have around. Usually
rows are from cache and you can get the same result after each
execution. On the other hand the first query returns different result
set every time.

If you know approximate number of rows in "items" table then you can
use this (and avoid sort):
select * from items where random() < 5.0 / total_rows limit 5

You can replace total_rows by this query: select reltuples from
pg_class where relname = 'items' (ie select * from items where random
> 5.0 / (select reltuples from pg_class where relname = 'items') limit
5)

--
Ondrej Ivanic
(ondrej.ivanic@gmail.com)