From: Bruno Wolff III <bruno@wolff.to>
To: Mario Splivalo <mario.splivalo@mobart.hr>
Cc: Mathieu Arnold <mat@mat.cc>
Cc: pgsql-sql@postgresql.org
Subject: Re: ORDER BY CASE ...
Date: Mon, 20 Feb 2006 11:33:26 -0600
Message-ID: <20060220173326.GA29604@wolff.to> (raw)
In-Reply-To: <1139866118.1647.2.camel@localhost.localdomain>
References: <1139844930.13054.23.camel@localhost.localdomain>
<9693350566E722C466358E08@[192.168.9.47]>
<1139845669.13054.28.camel@localhost.localdomain>
<B6687EF0F11B9302F7A56031@[192.168.9.47]>
<1139866118.1647.2.camel@localhost.localdomain>
On Mon, Feb 13, 2006 at 22:28:38 +0100,
Mario Splivalo <mario.splivalo@mobart.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, mario.splivalo@mobart.hr, mat@mat.cc
Subject: Re: ORDER BY CASE ...
In-Reply-To: <20060220173326.GA29604@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox