Re: sort output per alpha-numeric?
[email protected] (Sbob)
| Newsgroups | php.general |
|---|---|
| Message-ID | <[email protected]> |
Disregard, sorry wrong list
On 12/1/21 15:35, Sbob wrote:
>
> 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
>
>
>
>
>