sort output per alpha-numeric?

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

Responses

Browse pgsql-admin by date

  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