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

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
>