Re: ORDER BY CASE ...

On Mon, Feb 13, 2006 at 22:28:38 +0100,
Mario Splivalo <mario(dot)splivalo(at)mobart(dot)hr> wrote:
> Can't do so, because receiving_time is timestamptz, and "from" is
> varchar. There:
>
> pulitzer2=# select id, "from", receiving_time from messages order by
> case when 2=3 then "from" else receiving_time end desc limit 5;
> ERROR: CASE types timestamp with time zone and character varying cannot
> be matched
>
> I need to explicitly cast receiving_time into varchar.
>
> What I would like to include ASC/DESC into CASE, but I guess that's not
> possible.

If you describe what order you are trying to get we might be able to give
you a more specific suggestion.

It looks like you are trying to sort on different column numbers based
on whether or not two other column numbers (that weren't shown) are equal.
(The syntax you are using for doing this isn't correct, but it is the only
thing that seems to make sense.)

Since columns 2 and 3 aren't really comparable, how were you expecting
the rows for the two cases to be interspersed? Just converting a timestamp
to a string seem like it wouldn't be the right answer.