Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1msZ8o-0003bf-Ql for pgsql-admin@arkaria.postgresql.org; Wed, 01 Dec 2021 23:35:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1msZ8n-0003s2-6p for pgsql-admin@arkaria.postgresql.org; Wed, 01 Dec 2021 23:35:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1msZ8m-0003rs-Gi for pgsql-admin@lists.postgresql.org; Wed, 01 Dec 2021 23:35:56 +0000 Received: from hedgehog.birch.relay.mailchannels.net ([23.83.209.81]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1msZ8e-0003nn-Hl for pgsql-admin@lists.postgresql.org; Wed, 01 Dec 2021 23:35:55 +0000 X-Sender-Id: hostpapa|x-authuser|list.pg.gavan@pendari.org Received: from relay.mailchannels.net (localhost [127.0.0.1]) by relay.mailchannels.net (Postfix) with ESMTP id CC2096C07CD; Wed, 1 Dec 2021 23:35:44 +0000 (UTC) Received: from hp309.hostpapa.com (unknown [127.0.0.6]) (Authenticated sender: hostpapa) by relay.mailchannels.net (Postfix) with ESMTPA id 76C356C0733; Wed, 1 Dec 2021 23:35:43 +0000 (UTC) X-Sender-Id: hostpapa|x-authuser|list.pg.gavan@pendari.org Received: from hp309.hostpapa.com (hp309.hostpapa.com [69.90.160.170]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384) by 100.100.11.77 (trex/6.4.3); Wed, 01 Dec 2021 23:35:44 +0000 X-MC-Relay: Neutral X-MailChannels-SenderId: hostpapa|x-authuser|list.pg.gavan@pendari.org X-MailChannels-Auth-Id: hostpapa X-Abiding-Invention: 4b7f4309432799cb_1638401744592_2058501626 X-MC-Loop-Signature: 1638401744591:827754718 X-MC-Ingress-Time: 1638401744591 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=pendari.org ; s=default; h=Content-Transfer-Encoding:Content-Type:MIME-Version:References :In-Reply-To:Message-ID:Date:Subject:Cc:To:From:Sender:Reply-To:Content-ID: Content-Description:Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc :Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=8h8QTV6O5LEkh0Rx9l2ERo5dX2CmOefbDVJhEAcFRIw=; b=Ua8ck4UqdXW7emlYI0lWGQksjU ZCpkePRy8RFekaaXxHRFTUoRbG1yuY77/DM83I8wLuidwRB+4s4HeewhZdYeVUvDqRx00sInclHfv aYmBwX+HvaNGMG9ig7JvJZsMcHWxjpE06zVazIaSmiw4KDKs5axmUe133liBWjNLSkXJ1812kmrBM rrD6d007NIXJ2U7kzH7nnPlJBiHsMasRrQhBte+67sgf1lPejNLBnIMNiHltRtIWA9CvQ1Z0DC3CB 8j3EY1S0uJMVjKbmxHiYLYilAMqXYHAZ+DMXDY9PA/4DEskJialL0z4EjJ7NUI1NXI+mEdZepoTA9 e5EPg2+g==; Received: from [103.152.126.81] (port=41100 helo=[169.254.33.153]) by hp309.hostpapa.com with esmtpsa (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1msZ8Y-0031JS-C8; Thu, 02 Dec 2021 10:35:42 +1100 From: Gavan Schneider To: Pgsql-admin Cc: Sbob , "David G. Johnston" Subject: Re: sort output per alpha-numeric? Date: Thu, 02 Dec 2021 10:35:26 +1100 X-Mailer: MailMate (1.14r5850) Message-ID: <3ADA4F64-1223-44EF-97E1-0BCA1D9A643F@pendari.org> In-Reply-To: References: <6b1034cc-d98d-f3a7-6dd9-264bb0f934d8@quadratum-braccas.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_MailMate_4DB82363-EA54-49ED-9C9E-5177CDD6FFB6_=" Content-Transfer-Encoding: 8bit X-AuthUser: list.pg.gavan@pendari.org List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_MailMate_4DB82363-EA54-49ED-9C9E-5177CDD6FFB6_= Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 2 Dec 2021, at 9:50, David G. Johnston wrote: > On Wed, Dec 1, 2021 at 3:38 PM Sbob > 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., CREATE OR REPLACE FUNCTION image_size_rank ( spec TEXT ) RETURNS INTEGER LANGUAGE SQL AS $$ SELECT 1000 * substring( spec from '^([0-9]+)x.*' )::INTEGER + substring( spec from '^[0-9]+x([0-9]+)$' )::INTEGER ; $$; so — pendari=# SELECT image_size_rank ('19x14'); image_size_rank ----------------- 19014 (1 row) Another wrinkle in the human versus data domain is to constrain the print size specifications so the 1st dimension is always equal to or smaller(/larger) than the second (unless the order of the dimensions is relevant to orientation). The code snippet above makes 8x10 different to 10x8 but most of us who did prints in wet baths would consider them them equal. Gavan Schneider +61 405 124 883 dr.gavan.schneider@pendari.net --=_MailMate_4DB82363-EA54-49ED-9C9E-5177CDD6FFB6_= Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

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 11= x14, 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 nu= mbers into a single integer for ranking, e.g.,

CREATE OR REPLACE FUNCTION image_size_rank=
 ( spec TEXT )
RETURNS INTEGER
LANGUAGE SQL
AS
$$
	SELECT
		1000 * substring( spec from '^([0-9]+)x.*'  )::INTEGER
		+ substring( spec from '^[0-9]+x([0-9]+)$' )::INTEGER
;
$$;

so =E2=80=94

pendari=3D# SELECT image_size_rank ('19x14=
');
 image_size_rank
-----------------
           19014
(1 row)

Another wrinkle in the human versus data domain is to con= strain the print size specifications so the 1st dimension is always equal= to or smaller(/larger) than the second (unless the order of the dimensio= ns is relevant to orientation).

The code snippet above makes 8x10 different to 10x8 but m= ost of us who did prints in wet baths would consider them them equal.

=

Gavan Schneider
+61 405 124 883
dr.gavan.schneider@pendari.net

--=_MailMate_4DB82363-EA54-49ED-9C9E-5177CDD6FFB6_=--