From: Gavan Schneider <list.pg.gavan@pendari.org>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Cc: Sbob <sbob@quadratum-braccas.com>,
 "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>
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>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/1BB3B402-0456-4685-B633-86ECA5328892%40pendari.org>
Precedence: bulk

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.o=
rg>
> 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 11x=
14,
>> etc)
>>
>> Is there an easy way to do this?
>>
>> You can sort by an expression. For the data as shown the following sho=
uld
>>
>> 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-di=
git
> 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/standa=
rd-photo-sizes/

This isn=E2=80=99t too big a deal provided the sizes end up in the order =
the OP wants so the more elegant substituting of  =E2=80=98x=E2=80=99 wil=
l generate the correct order as far as I can tell from superficial inspec=
tion.

In my own (more pedestrian) way I would have these sizes as their own tab=
le with ranking as a column (i.e., calculate once and store), and use tha=
t table to enforce a foreign key constraint to prevent any rogue sizes ge=
tting into the customer relevant tables. Then the ranking number is total=
ly arbitrary. Of course good practice is to allow 10, or more, between nu=
mbers because there is always that something which comes along later.  Th=
e job is small enough that is can be done quicker by hand.

Gavan Schneider
=E2=80=94=E2=80=94
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a wel=
l-known solution to every human problem =E2=80=94 neat, plausible, and wr=
ong.
=E2=80=94 H. L. Mencken, 1920