Have you considered use of the "nulls last" option in order by (https://www.postgresql.org/docs/13/queries-order.html)?
Alternatively, you could write your own type, with its own ordering primitive 😉
On Sun, 30 May 2021, 12:15 am Laura Smith, <n5d9xq3ti233xiyif2vp@xxxxxxxxxxxxx> wrote:
Hi
I've got a bit of a puzzle that I'm not quite sure how to approach.
Let's say I've got a table of bios, so :
create table bios (
first_name text not null,
last_name text not null,
person_title text,
person_short_bio text
);
Now, the "natural order" would be a standard "select * from bios order by last_name". Basic stuff, no problem.
The problem is that my use-case calls for a scenario where due to protocol certain people may be designated as "VIP" and therefore need to appear at the top. In addition, protocol may dictate that those "VIP" people themselves may (sometimes but not always) need to be ordered in a specific manner.
Bear in mind that there may be a large enough number of people in this table that the naïve approach of manually assigning everyone an order is neither practical or desirable. Hence the need for an "override" which would mean only a subset of people would need specific parameters.
Any ideas ?
Thanks !
Laura
