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 B67699DCBCC for ; Mon, 13 Feb 2006 17:30:32 -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 21449-09 for ; Mon, 13 Feb 2006 17:30:34 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id BF5F09DCB9B for ; Mon, 13 Feb 2006 17:30:30 -0400 (AST) Received: from ls405.htnet.hr (ls405.htnet.hr [195.29.150.97]) by svr4.postgresql.org (Postfix) with ESMTP id 0EFE35AF07D for ; Mon, 13 Feb 2006 21:30:28 +0000 (GMT) Received: from ls422.t-com.hr (ls422.t-com.hr [195.29.150.237]) by ls405.htnet.hr (0.0.0/8.12.10) with ESMTP id k1DLUHSr023578; Mon, 13 Feb 2006 22:30:22 +0100 Received: from ls422.t-com.hr (localhost.localdomain [127.0.0.1]) by ls422.t-com.hr (Qmlai) with ESMTP id D3FFF988045; Mon, 13 Feb 2006 22:30:21 +0100 (CET) X-Envelope-Sender: mario.splivalo@mobart.hr X-Envelope-Sender: mario.splivalo@mobart.hr Received: from ls422.t-com.hr (localhost.localdomain [127.0.0.1]) by ls422.t-com.hr (Qmlai) with ESMTP id D0F3A98803F; Mon, 13 Feb 2006 22:30:21 +0100 (CET) Received: from 83-131-51-31.adsl.net.t-com.hr (83-131-51-31.adsl.net.t-com.hr [83.131.51.31]) by ls422.t-com.hr (Qmlai) with ESMTP id 8A3628B8055; Mon, 13 Feb 2006 22:30:21 +0100 (CET) Subject: Re: ORDER BY CASE ... From: Mario Splivalo Reply-To: mario.splivalo@mobart.hr To: Mathieu Arnold Cc: pgsql-sql@postgresql.org In-Reply-To: References: <1139844930.13054.23.camel@localhost.localdomain> <9693350566E722C466358E08@[192.168.9.47]> <1139845669.13054.28.camel@localhost.localdomain> Content-Type: text/plain Organization: Mob-Art Date: Mon, 13 Feb 2006 22:28:38 +0100 Message-Id: <1139866118.1647.2.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.162 required=5 tests=[AWL=0.162] X-Spam-Score: 0.162 X-Spam-Level: X-Archive-Number: 200602/142 X-Sequence-Number: 24152 On Mon, 2006-02-13 at 17:10 +0100, Mathieu Arnold wrote: > | It works like this: > | > | ORDER BY (CASE WHEN 5=5 THEN "from"::varchar ELSE > | receiving_time::varchar) DESC. > | > | Is there a way to have DESC/ASC inside of a CASE? > | > | Mario > > No, you don't understand, you should do something like : > > case when foo = bar then from else receiving_time desc end > 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. Mike