Thread: Sorting by constant values

I have a column that I want to sort by certain values. The values are
Unit, Exterior and Common. I want all the records with Unit first,
Common second and Exterior last in the sort order. These are the only 3
possible values, is there a way to sort manually like that with the
alphanumeric values?

--
Robert

You can order by conditions, lets say column='Unit'. The evaluation of a conditions will give you 't' or 'f', and alfabetically 'f' < 't'... you should use DESC to get the matches first. So, it would be more or less like this:

ORDER BY
   column='Unit' DESC,
   column='Exterior' DESC,
   column='Common' DESC

I don't think this is performant though. If you have many rows to evaluate, you could create a funtion like this:
CREATE FUNCTION evaluate(TEXT) RETURNS TEXT LANGUAGE 'sql' AS '
   SELECT $1='Unit' || $1='Exterior' || $1='Common';
'
This function would return something like 'tff', 'ftf', 'fft', and you should be able to create an index on that function. Then you can use the index to order your rows.

Hope it helps ;)

On Tue, 2005-05-03 at 12:29, Robert Fitzpatrick wrote:
> I have a column that I want to sort by certain values. The values are
> Unit, Exterior and Common. I want all the records with Unit first,
> Common second and Exterior last in the sort order. These are the only 3
> possible values, is there a way to sort manually like that with the
> alphanumeric values?

case statement should work.

http://www.postgresql.org/docs/8.0/static/functions-conditional.html

On Tue, 2005-05-03 at 13:29 -0400, Robert Fitzpatrick wrote:
> I have a column that I want to sort by certain values. The values are
> Unit, Exterior and Common. I want all the records with Unit first,
> Common second and Exterior last in the sort order. These are the only 3
> possible values, is there a way to sort manually like that with the
> alphanumeric values?

... ORDER BY length(col);

:-)
gnari