X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 2FA169DC9F5 for ; Mon, 20 Feb 2006 13:24:06 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 39244-09 for ; Mon, 20 Feb 2006 13:24:05 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id 599849DC9AC for ; Mon, 20 Feb 2006 13:24:03 -0400 (AST) Received: (qmail 31303 invoked by uid 500); 20 Feb 2006 17:33:26 -0000 Date: Mon, 20 Feb 2006 11:33:26 -0600 From: Bruno Wolff III To: Mario Splivalo Cc: Mathieu Arnold , pgsql-sql@postgresql.org Subject: Re: ORDER BY CASE ... Message-ID: <20060220173326.GA29604@wolff.to> Mail-Followup-To: Bruno Wolff III , Mario Splivalo , Mathieu Arnold , pgsql-sql@postgresql.org References: <1139844930.13054.23.camel@localhost.localdomain> <9693350566E722C466358E08@[192.168.9.47]> <1139845669.13054.28.camel@localhost.localdomain> <1139866118.1647.2.camel@localhost.localdomain> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1139866118.1647.2.camel@localhost.localdomain> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.094 required=5 tests=[AWL=0.094] X-Spam-Score: 0.094 X-Spam-Level: X-Archive-Number: 200602/249 X-Sequence-Number: 24259 On Mon, Feb 13, 2006 at 22:28:38 +0100, Mario Splivalo 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.