Обсуждение: way to custom sort column by fixed strings, then by field's content

On 02/03/2014 10:53 AM, Susan Cassidy wrote:
> I have a column that contains items like
> 'absolute root'
> 'root 3'
> 'root 4'
> 'root 5'
> 'scene 1'
> 'scene 2'
> 'scene 3'
>
> and I would like them to sort in that order.
>
> I tried:
> select sti.description, sc.description from scene_thing_instances sti
> join scenes sc on sti.scene_id = sc.scene_id
>    order by CASE sc.description
>              when (sc.description = 'absolute root'::text) then 1
>              when (sc.description ilike 'root%') then  2
>              else 3
>             END;
>
> I was starting with this, and was going to add perhaps another case
> statement.
>
> But it gives me:
> ERROR:  operator does not exist: text = boolean
> LINE 3:             when (sc.description = 'absolute root'::text) th...
>                      ^
> HINT:  No operator matches the given name and argument type(s). You
> might need to add explicit type casts.
>
> I don't understand this because description is a text column, not
> boolean, and certainly 'absolute root'::text is a text string.
>
> This is 9.2.
>
> Ideas, anyone?

select sti.description, sc.description from scene_thing_instances sti
join scenes sc on sti.scene_id = sc.scene_id
   order by sc.description, CASE
             when (sc.description = 'absolute root'::text) then 1
             when (sc.description ilike 'root%') then  2
             else 3
            END;

>
> Thanks,
> Susan


--
Adrian Klaver
adrian.klaver@gmail.com

On 02/03/2014 12:01 PM, Susan Cassidy wrote:

CCing the list.

> This didn't sort the way I want.  It ended up as:
>      description    | description
> -------------------+-------------
>   18 cm long wrench | Scene 1
>   absolute root     |
>   blue screwdriver  | Scene 1
>   red toolbox       | Scene 1
>   root 3            | Scene 1
>   root 4            | Scene 2
>   root 6            | Scene 3
>   small wrench      | Scene 1
>   tire              | Scene 2
>
>
> The first column is the one I want sorted.

Probably best to show us a made up sample of what you want.

>
> Also note that I made it a left outer join\

Where?

>
> Susan
>



--
Adrian Klaver
adrian.klaver@gmail.com

On 02/03/2014 12:01 PM, Susan Cassidy wrote:

CCing the list.

This didn't sort the way I want.  It ended up as:
     description    | description
-------------------+-------------
  18 cm long wrench | Scene 1
  absolute root     |
  blue screwdriver  | Scene 1
  red toolbox       | Scene 1
  root 3            | Scene 1
  root 4            | Scene 2
  root 6            | Scene 3
  small wrench      | Scene 1
  tire              | Scene 2


The first column is the one I want sorted.

Probably best to show us a made up sample of what you want.

Also note that I made it a left outer join\

Where?

Susan

With which query?

On 02/03/2014 12:01 PM, Susan Cassidy wrote:

CCing the list.

This didn't sort the way I want.  It ended up as:
     description    | description
-------------------+-------------
  18 cm long wrench | Scene 1
  absolute root     |
  blue screwdriver  | Scene 1
  red toolbox       | Scene 1
  root 3            | Scene 1
  root 4            | Scene 2
  root 6            | Scene 3
  small wrench      | Scene 1
  tire              | Scene 2


The first column is the one I want sorted.

Probably best to show us a made up sample of what you want.

Also note that I made it a left outer join\

Where?

Susan

Is this what you're after?
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;
        s1         |   s2    | v
-------------------+---------+---
 absolute root     |         | 1
 18 cm long wrench | Scene 1 | 3
 blue screwdriver  | Scene 1 | 3
 red toolbox       | Scene 1 | 3
 root 3            | Scene 1 | 3
 root 4            | Scene 2 | 3
 root 6            | Scene 3 | 3
 small wrench      | Scene 1 | 3
 tire              | Scene 2 | 3
(9 rows)

    description    | description
-------------------+-------------
 18 cm long wrench | Scene 1
 absolute root     |
 blue screwdriver  | Scene 1
 red toolbox       | Scene 1
 root 3            | Scene 1
 root 4            | Scene 2
 root 6            | Scene 3
 small wrench      | Scene 1
 tire              | Scene 2

On Mon, Feb 3, 2014 at 11:14 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On 02/03/2014 10:53 AM, Susan Cassidy wrote:

I have a column that contains items like
'absolute root'
'root 3'
'root 4'
'root 5'
'scene 1'
'scene 2'
'scene 3'

and I would like them to sort in that order.

I tried:
select sti.description, sc.description from scene_thing_instances sti
join scenes sc on sti.scene_id = sc.scene_id
   order by CASE sc.description
             when (sc.description = 'absolute root'::text) then 1
             when (sc.description ilike 'root%') then  2
             else 3
            END;

I was starting with this, and was going to add perhaps another case
statement.

But it gives me:
ERROR:  operator does not exist: text = boolean
LINE 3:             when (sc.description = 'absolute root'::text) th...
                     ^
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.

I don't understand this because description is a text column, not
boolean, and certainly 'absolute root'::text is a text string.

This is 9.2.

Ideas, anyone?

select sti.description, sc.description from scene_thing_instances sti join scenes sc on sti.scene_id = sc.scene_id
  order by sc.description, CASE
            when (sc.description = 'absolute root'::text) then 1
            when (sc.description ilike 'root%') then  2
            else 3
           END;

Thanks,
Susan

--
Adrian Klaver
adrian.klaver@gmail.com

Sorry this is what I meant to post

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)

Susan Cassidy-3 wrote
> I have a column that contains items like
> 'absolute root'
> 'root 3'
> 'root 4'
> 'root 5'
> 'scene 1'
> 'scene 2'
> 'scene 3'
>
> and I would like them to sort in that order.
>
> I tried:
> select sti.description, sc.description from scene_thing_instances sti join
> scenes sc on sti.scene_id = sc.scene_id
>   order by CASE sc.description
>             when (sc.description = 'absolute root'::text) then 1
>             when (sc.description ilike 'root%') then  2
>             else 3
>            END;
>
> I was starting with this, and was going to add perhaps another case
> statement.
>
> But it gives me:
> ERROR:  operator does not exist: text = boolean
> LINE 3:             when (sc.description = 'absolute root'::text) th...
>                     ^
> HINT:  No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
>
> I don't understand this because description is a text column, not boolean,
> and certainly 'absolute root'::text is a text string.
>
> This is 9.2.
>
> Ideas, anyone?

Read the documentation for "CASE":

http://www.postgresql.org/docs/9.3/interactive/functions-conditional.html#FUNCTIONS-CASE

There are two forms:

SF) CASE expression WHEN value THEN result
LF) CASE WHEN condition THEN result

The first form is a short-hand version for the second form using the common
equality condition.  Converting from SF to LF results in a condition of the
form "expression = value" and thus there must be an equality operator
between the type of "expression" and the type of "value".  In your example
"expression" is a string - description - while "value" is a boolean (string
.op. string).  This is because you incorrectly repeated the writing of the
"expression" in each "WHEN" clause.

For your problem you want to explicitly use the long-form so you have much
more flexibility in your "conditions" than simple value-equality (i.e., your
ilike is not possible in short-form)

ORDER BY CASE WHEN sc.desc... = 'absol...' THEN 1 WHEN sc.desc... ilike
'root%' THEN 2 ELSE 3 END

This causes the primary sort just like what you want.  However, you have not
specified how multiple "root" items should sort nor how everything else
besides "root" and "absolute root" should sort.  To do so you add a second
sort expression - in this case just the description column.

ORDER BY CASE ... END, sc.description

Now all roots will be listed in ascending string order after "absolute root"
and everything else will come after "root%" also in ascending string order.

Adrian was close but simply reversed the order of the two expressions in the
ORDER BY.  More importantly, though, he did provide the correct "CASE"
syntax. The typo of the column ordering was easily overlooked, and corrected
for by the reader IMO, given the data sample in which the whole case part
ended up irrelevant.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/way-to-custom-sort-column-by-fixed-strings-then-by-field-s-content-tp5790371p5790398.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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