From: Gavan Schneider <list.pg.gavan@pendari.org>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Cc: Sbob <sbob@quadratum-braccas.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Subject: Re: sort output per alpha-numeric?
Date: Thu, 02 Dec 2021 11:51:35 +1100
Message-ID: <1BB3B402-0456-4685-B633-86ECA5328892@pendari.org> (raw)
In-Reply-To: <CAKFQuwb3n6gvEPB_w=RuV3kW2QmNXoEOqH7yyumJatj6aSAtwA@mail.gmail.com>
References: <6b1034cc-d98d-f3a7-6dd9-264bb0f934d8@quadratum-braccas.com>
<CAKFQuwYL_gQ9JoPehdCj8NKevW77636xSNr2PvSR_BS+8_KhnQ@mail.gmail.com>
<3ADA4F64-1223-44EF-97E1-0BCA1D9A643F@pendari.org>
<CAKFQuwb3n6gvEPB_w=RuV3kW2QmNXoEOqH7yyumJatj6aSAtwA@mail.gmail.com>
On 2 Dec 2021, at 10:55, David G. Johnston wrote:
> On Wed, Dec 1, 2021 at 4:35 PM Gavan Schneider <list.pg.gavan@pendari.org>
> wrote:
>
>> On 2 Dec 2021, at 9:50, David G. Johnston wrote:
>>
>> On Wed, Dec 1, 2021 at 3:38 PM Sbob <sbob@quadratum-braccas.com> wrote:
>>
>> I want the displayed print\_size to be ordered by size (8x10, then 11x14,
>> etc)
>>
>> Is there an easy way to do this?
>>
>> You can sort by an expression. For the data as shown the following should
>>
>> A little more heavy handed might be to convert the two numbers into a
>> single integer for ranking, e.g.,
>>
> Good idea, but "replace(print_size, 'x', '0')::integer" seems like a
> simpler implementation - though I'd want to double-check some single-digit
> scenarios before making a final choice (if there are any besides "8" to
> worry about).
>
This piqued my interest and confirmed several sizes in the single digit (inch) category are considered standard: https://pikwizard.com/blog/standard-photo-sizes/
This isn’t too big a deal provided the sizes end up in the order the OP wants so the more elegant substituting of ‘x’ will generate the correct order as far as I can tell from superficial inspection.
In my own (more pedestrian) way I would have these sizes as their own table with ranking as a column (i.e., calculate once and store), and use that table to enforce a foreign key constraint to prevent any rogue sizes getting into the customer relevant tables. Then the ranking number is totally arbitrary. Of course good practice is to allow 10, or more, between numbers because there is always that something which comes along later. The job is small enough that is can be done quicker by hand.
Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a well-known solution to every human problem — neat, plausible, and wrong.
— H. L. Mencken, 1920
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: list.pg.gavan@pendari.org, pgsql-admin@lists.postgresql.org, sbob@quadratum-braccas.com, david.g.johnston@gmail.com
Subject: Re: sort output per alpha-numeric?
In-Reply-To: <1BB3B402-0456-4685-B633-86ECA5328892@pendari.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox