ORDER with CASE and Random for each case

From: Alex Magnum <magnum11200(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: ORDER with CASE and Random for each case
Date: 2017-10-16 15:39:38
Message-ID: CA+cR4zexyuczp8q6A+atN2ke3TWWWufknKT_pfZg=VCPE-K+Eg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

If have a view that I would like to sort where I divide the return in 3
different groups.
These 3 groups then should have a random sort order each.
As I am I using it with an offset, and limit, the randomness should be the
same.

For example:
SELECT user_id, age FROM view_users
ORDER BY CASE WHEN age < 20 THEN 1
WHEN age < 50 THEN 2
ELSE 3
END
OFFSET 0 LIMIT 20;

If I have for each age group 30 users. I want these 3 groups to be ordered
randomly but during the paging maintain the order.

The way I would do it now is to use setseed() and a union of 3 selects with
the 3 conditions and random() in each of the three.

Is there a better and more efficient way to do it in one query?

Thanks for any suggestions

A

Browse pgsql-general by date

  From Date Subject
Next Message Igal @ Lucee.org 2017-10-16 15:58:50 Re: Delete Duplicates with Using
Previous Message Seamus Abshere 2017-10-16 15:34:08 Re: Non-overlapping updates blocking each other