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

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.