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

Поиск
Список
Период
Сортировка
От Susan Cassidy
Тема way to custom sort column by fixed strings, then by field's content
Дата
Msg-id CAE3Q8omhEHQVc673+3b1vZUzW_JpdbM5wWq=1nu2pCRp3OVtkA@mail.gmail.com
обсуждение исходный текст
Ответы Re: way to custom sort column by fixed strings, then by field's content  (Adrian Klaver <adrian.klaver@gmail.com>)
Re: way to custom sort column by fixed strings, then by field's content  (salah jubeh <s_jubeh@yahoo.com>)
Re: way to custom sort column by fixed strings, then by field's content  (David Johnston <polobo@yahoo.com>)
Список pgsql-general
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?

Thanks,
Susan

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Drop all overloads of a function without knowing parameter types
Следующее
От: Evan Martin
Дата:
Сообщение: Re: Drop all overloads of a function without knowing parameter types