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
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 |