From: | Sbob <sbob(at)quadratum-braccas(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | sort output per alpha-numeric? |
Date: | 2021-12-01 22:37:56 |
Message-ID: | 6b1034cc-d98d-f3a7-6dd9-264bb0f934d8@quadratum-braccas.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2021-12-01 22:50:11 | Re: sort output per alpha-numeric? |
Previous Message | Tom Lane | 2021-12-01 16:16:45 | Re: Suggestion: Explain how to move objects to the default tablespace in sql-altertable.html |