Обсуждение: sort output per alpha-numeric?
All;
I have a set of tables that looks like this:
\d portfolio_sc_images_print_size_asc
Table "public.portfolio_sc_images_print_size_asc"
Column | Type | Collation | Nullable | Default
------------------------+--------+-----------+----------+---------
portfolio_sc_images_id | bigint | | not null |
print_type_id | bigint | | not null |
print_size_id | bigint | | not null |
Indexes:
"portfolio_sc_images_print_size_asc_pkey" PRIMARY KEY, btree (portfolio_sc_images_id, print_type_id, print_size_id)
Foreign-key constraints:
"portfolio_sc_images_print_size_asc_portfolio_sc_images_id_fkey" FOREIGN KEY (portfolio_sc_images_id) REFERENCES portfolio_
sc_images(portfolio_sc_images_id)
"portfolio_sc_images_print_size_asc_print_size_id_fkey" FOREIGN KEY (print_size_id) REFERENCES print_sizes(print_size_id)
"portfolio_sc_images_print_size_asc_print_type_id_fkey" FOREIGN KEY (print_type_id) REFERENCES print_types(print_type_id)
\d print_sizes
Table "public.print_sizes"
Column | Type | Collation | Nullable | Default
---------------+------------------------+-----------+----------+----------------------------------------------------
print_size_id | bigint | | not null | nextval('print_sizes_print_size_id_seq'::regclass)
print_size | character varying(100) | | |
Indexes:
"print_sizes_pkey" PRIMARY KEY, btree (print_size_id)
Referenced by:
TABLE "portfolio_sc_images_print_size_asc" CONSTRAINT "portfolio_sc_images_print_size_asc_print_size_id_fkey" FOREIGN KEY (
print_size_id) REFERENCES print_sizes(print_size_id)
\d print_types
Table "public.print_types"
Column | Type | Collation | Nullable | Default
---------------+------------------------+-----------+----------+----------------------------------------------------
print_type_id | bigint | | not null | nextval('print_types_print_type_id_seq'::regclass)
print_type | character varying(100) | | |
Indexes:
"print_types_pkey" PRIMARY KEY, btree (print_type_id)
Referenced by:
TABLE "portfolio_sc_images_print_size_asc" CONSTRAINT "portfolio_sc_images_print_size_asc_print_type_id_fkey" FOREIGN KEY (
print_type_id) REFERENCES print_types(print_type_id)
TABLE "print_finishes" CONSTRAINT "print_finishes_print_type_id_fkey" FOREIGN KEY (print_type_id) REFERENCES print_types(pr
int_type_id)
Below are the table contents.
When I run this query:
select print_size from print_sizes where print_size_id in (select print_size_id from portfolio_sc_images_print_size_asc
where portfolio_sc_images_id = 1 and print_type_id = (select print_type_id from print_types where print_type = 'Loose Fine Art
Print')) order by print_size asc;
I get a list like this:
print_size
------------
11x14
16x20
20x24
24x30
32x40
40x50
8x10
(7 rows)
I want the displayed print_size to be ordered by size (8x10, then 11x14, etc)
Is there an easy way to do this?
Thanks in advance
Table contents:
select * from portfolio_sc_images_print_size_asc;
portfolio_sc_images_id | print_type_id | print_size_id
------------------------+---------------+---------------
1 | 1 | 7
1 | 1 | 10
1 | 1 | 15
1 | 1 | 37
1 | 1 | 30
1 | 2 | 7
1 | 2 | 10
1 | 2 | 15
1 | 2 | 18
1 | 2 | 37
1 | 2 | 38
1 | 2 | 30
\x
Expanded display is on.
pixdb=> select * from portfolio_sc_images;
-[ RECORD 1 ]--------------+----------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------
portfolio_sc_images_id | 1
portfolio_subcategories_id | 1
image_location | /Portfolio/Night_Skies/7009
image_name | DSC_2989-Hebrews-1-3.jpg
image_display_name | Image 7009 - Hebrews 1:3
desc_title | Rocky Mountain National Park - Sprague Lake - Hebrews 1:3
desc_text | Rocky Mountain National Park at Sprague Lake, an amazing calm night, GOD called forth the Milky Way and it was incredible
display_order | 1
orig_image_name |
gimp_image_name |
print_image_name |
-[ RECORD 2 ]--------------+----------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------
portfolio_sc_images_id | 2
portfolio_subcategories_id | 1
image_location | /Portfolio/Night_Skies/7009
image_name | DSC_2989.jpg
image_display_name | Image 7009
desc_title | Rocky Mountain National Park - Sprague Lake
desc_text | Rocky Mountain National Park at Sprague Lake, an amazing calm night, GOD called forth the Milky Way and it was incredible
display_order | 2
orig_image_name |
gimp_image_name |
print_image_name |
select * from print_sizes;
print_size_id | print_size
---------------+------------
7 | 8x10
8 | 8x12
9 | 8x24
10 | 11x14
11 | 11x17
12 | 12x18
13 | 12x24
14 | 12x36
15 | 16x20
16 | 16x24
17 | 16x30
18 | 20x24
19 | 20x30
20 | 20x40
21 | 20x50
22 | 20x60
23 | 24x36
24 | 24x48
25 | 30x40
26 | 30x45
27 | 30x50
28 | 30x60
29 | 30x90
30 | 40x50
31 | 40x60
32 | 40x70
33 | 40x80
34 | 40x90
35 | 48x72
36 | 48x96
37 | 24x30
38 | 32x40
I get a list like this:
print_size
------------
11x14
16x20
20x24
24x30
32x40
40x50
8x10
(7 rows)I want the displayed print_size to be ordered by size (8x10, then 11x14, etc)
Is there an easy way to do this?
On Wed, Dec 1, 2021 at 3:38 PM Sbob <sbob@quadratum-braccas.com> wrote:I get a list like this:
print_size
------------
11x14If this isn't sufficient (e.g., if the second dimension causes the issue) you may need to break the two-part string into two separate fields, convert them to integers, and then sort on the pair.
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.,
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
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.,
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’ willgenerate 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 onceand store), and use that table to enforce a foreign key constraint to prevent any rogue sizes getting into the customerrelevant tables. Then the ranking number is totally arbitrary. Of course good practice is to allow 10, or more, betweennumbers because there is always that something which comes along later. The job is small enough that is can be donequicker 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
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).David J.
Natural sort may be less invasive.
order by length(print_size), print_size
On Dec 1, 2021, at 6:24 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:the normalized version of this model would store the width and height measurements separately and your print_size text would be a presentation issue.