Re: way to custom sort column by fixed strings, then by field's content - Mailing list pgsql-general

On 02/03/2014 01:00 PM, Susan Cassidy wrote:
>
> The query is currently:
> select sti.description, sc.description from scene_thing_instances sti
>     left outer join scenes sc on sti.scene_id = sc.scene_id
>    order by sti.description, CASE
>              when (sti.description = 'absolute root'::text) then 1
>              when (sti.description ilike 'root%') then  2
>              else 3
>             END;
>
>
> The results I want are:
>
>      description    | description
> -------------------+-------------
>
>   absolute root     |
>   root 3            | Scene 1
>   root 4            | Scene 2
>   root 6            | Scene 3
> 18 cm long wrench | Scene 1
> blue screwdriver  | Scene 1
> red toolbox       | Scene 1
> small wrench      | Scene 1
>   tire              | Scene 2
> (9 rows)
>

So Robs last solution:

select s.s1, s.s2, (    CASE
             when (s.s1 = 'absolute root'::text) then 1
             when (s.s1 ~* '^root*') then  2
             else 3
            END) as v
from scripts as s
   order by v,s1

toys-# ;
         s1         |   s2    | v
-------------------+---------+---
  absolute root     |         | 1
  root 3            | Scene 1 | 2
  root 4            | Scene 2 | 2
  root 6            | Scene 3 | 2
  18 cm long wrench | Scene 1 | 3
  blue screwdriver  | Scene 1 | 3
  red toolbox       | Scene 1 | 3
  small wrench      | Scene 1 | 3
  tire              | Scene 2 | 3
(9 rows)


--
Adrian Klaver
adrian.klaver@gmail.com