Обсуждение: Overriding natural order of query results for a subset
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" andtherefore need to appear at the top. In addition, protocol may dictate that those "VIP" people themselves may (sometimesbut 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 assigningeveryone an order is neither practical or desirable. Hence the need for an "override" which would mean only a subsetof people would need specific parameters. Any ideas ? Thanks ! Laura
On Saturday, May 29, 2021, Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch> wrote:
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.
Add whatever attribute(s) determine vip status to your order by clause.
David J.
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@protonmail.ch> 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
I did try "nulls last" but will give it another go, maybe I messed up on the ordering of clauses. Sent with ProtonMail Secure Email. ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Saturday, 29 May 2021 15:23, Michael van der Kolff <mvanderkolff@gmail.com> wrote: > 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@protonmail.ch> 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 (sometimesbut 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 assigningeveryone an order is neither practical or desirable. Hence the need for an "override" which would mean only a subsetof people would need specific parameters. > > > > Any ideas ? > > > > Thanks ! > > > > Laura
On 5/29/21 9:00 AM, Laura Smith wrote: > I did try "nulls last" but will give it another go, maybe I messed up on the ordering of clauses. Unless the fields you are ordering on contain NULLs I'm not sure how this is going to deal with your issue. > > Sent with ProtonMail Secure Email. > > ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ > On Saturday, 29 May 2021 15:23, Michael van der Kolff <mvanderkolff@gmail.com> wrote: > >> 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@protonmail.ch> 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 (sometimesbut 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 assigningeveryone an order is neither practical or desirable. Hence the need for an "override" which would mean only a subsetof people would need specific parameters. >>> >>> Any ideas ? >>> >>> Thanks ! >>> >>> Laura > > -- Adrian Klaver adrian.klaver@aklaver.com
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Saturday, 29 May 2021 17:06, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 5/29/21 9:00 AM, Laura Smith wrote: > > > I did try "nulls last" but will give it another go, maybe I messed up on the ordering of clauses. > > Unless the fields you are ordering on contain NULLs I'm not sure how > this is going to deal with your issue. > Reading between the lines of the poster who suggested it, I'm guessing the suggestion was to add an "int" column, most ofwhich is null except for numbers where needed for ordering and then having "order by vip_num_order,order by last_name"in my select clause.
On 5/29/21 9:34 AM, Laura Smith wrote: > > ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ > On Saturday, 29 May 2021 17:06, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > >> On 5/29/21 9:00 AM, Laura Smith wrote: >> >>> I did try "nulls last" but will give it another go, maybe I messed up on the ordering of clauses. >> >> Unless the fields you are ordering on contain NULLs I'm not sure how >> this is going to deal with your issue. >> > > > Reading between the lines of the poster who suggested it, I'm guessing the suggestion was to add an "int" column, mostof which is null except for numbers where needed for ordering and then having "order by vip_num_order,order by last_name"in my select clause. > That's a whole lot of reading:) If you are going to go that route use NOT NULL and a DEFAULT of 0. Then you are not depending on a lack of information and you can use explicit number setting to create your ordering. -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, May 29, 2021 at 9:15 AM Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch> 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
)
You don't have a VIP field, so how do you know who's a VIP and who isn't much less who's a VVIP? Is that information buried in the title and short bio fields?
You probably need some kind order by case when .... else .... end clause, where the else clause deals with the non-VIPs, probably negating the need for a nulls last clause.
Michael Nolan <htfoot@gmail.com> writes:
> You probably need some kind order by case when .... else .... end clause,
> where the else clause deals with the non-VIPs, probably negating the need
> for a nulls last clause.
The idiomatic way to do this, assuming that you create an "is_vip bool"
field or some other way to identify VIPs accurately, is
ORDER BY is_vip DESC, last_name, first_name
relying on the fact that bool TRUE > bool FALSE.
regards, tom lane
Sent with ProtonMail Secure Email. ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Saturday, 29 May 2021 17:55, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Michael Nolan htfoot@gmail.com writes: > > > You probably need some kind order by case when .... else .... end clause, > > where the else clause deals with the non-VIPs, probably negating the need > > for a nulls last clause. > > The idiomatic way to do this, assuming that you create an "is_vip bool" > field or some other way to identify VIPs accurately, is > > ORDER BY is_vip DESC, last_name, first_name > > relying on the fact that bool TRUE > bool FALSE. > > regards, tom lane Thanks tom ! I think yours combined with Adrian's "DEFAULT of 0" is likely to be the winner. Doing some experimenting, it gives me three options: - Leave field as default = default name alphabetic - Add VIPs with same integer = VIPs at the top, ordered alphabetically - Add VIPs with differing integers = VIPs ordered by protocol Thanks all.