Re: way to custom sort column by fixed strings, then by field's content

Works great, thanks a bunch.

Susan

On Mon, Feb 3, 2014 at 12:39 PM, Rob Sargent <robjsargent(at)gmail(dot)com> wrote:

> On 02/03/2014 01:01 PM, Susan Cassidy wrote:
>
> 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(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)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)
>
>