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 169859DC99F for ; Mon, 13 Feb 2006 11:35:31 -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 41414-03 for ; Mon, 13 Feb 2006 11:35:35 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from pjetlic.mobart.hr (pjetlic.mobart.hr [80.80.51.10]) by postgresql.org (Postfix) with ESMTP id 075409DCC24 for ; Mon, 13 Feb 2006 11:35:27 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by pjetlic.mobart.hr (Postfix) with ESMTP id E085417A8B for ; Mon, 13 Feb 2006 16:35:30 +0100 (CET) Received: from pjetlic.mobart.hr ([127.0.0.1]) by localhost (pjetlic [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 03750-01-3 for ; Mon, 13 Feb 2006 16:35:30 +0100 (CET) Received: from mike.mobart.hr (mike.mobart.hr [192.168.10.94]) (using SSLv3 with cipher RC4-MD5 (128/128 bits)) (No client certificate requested) by pjetlic.mobart.hr (Postfix) with ESMTP id C429F17A89 for ; Mon, 13 Feb 2006 16:35:30 +0100 (CET) Subject: ORDER BY CASE ... From: Mario Splivalo Reply-To: mario.splivalo@mobart.hr To: pgsql-sql@postgresql.org Content-Type: text/plain Organization: Mob-Art Date: Mon, 13 Feb 2006 16:35:30 +0100 Message-Id: <1139844930.13054.23.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by OpenSource AV+AS Mail Server X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.679 required=5 tests=[AWL=-0.487, SUBJ_ALL_CAPS=1.166] X-Spam-Score: 0.679 X-Spam-Level: X-Archive-Number: 200602/131 X-Sequence-Number: 24141 Am I misusing the ORDER BY with CASE, or, what? :) I have a table, messages, half dozen of columns, exposing here just three of them: pulitzer2=# select id, "from", receiving_time from messages where service_id = 20 order by case when 5=5 then 2 else 3 end desc limit 5; id | from | receiving_time --------+---------------+------------------------ 869585 | +385989095824 | 2005-12-08 16:04:23+01 816579 | +385915912312 | 2005-11-23 17:51:06+01 816595 | +38598539263 | 2005-11-23 17:58:21+01 816594 | +385915929232 | 2005-11-23 17:57:30+01 816589 | +385912538567 | 2005-11-23 17:54:32+01 (5 rows) pulitzer2=# select id, "from", receiving_time from messages where service_id = 20 order by case when 5=6 then 2 else 3 end desc limit 5; id | from | receiving_time --------+---------------+------------------------ 869585 | +385989095824 | 2005-12-08 16:04:23+01 816579 | +385915912312 | 2005-11-23 17:51:06+01 816595 | +38598539263 | 2005-11-23 17:58:21+01 816594 | +385915929232 | 2005-11-23 17:57:30+01 816589 | +385912538567 | 2005-11-23 17:54:32+01 (5 rows) I tought I'd get differently sorted data, since in the first query I said 5=5, and in second I said 5=6. Is this a right way to use CASE on ORDER BY, or? I need to sord the data in the function depending on the function parametar. If it's true, randomize the sort, if not, sort by receiving_time, newest first. So, can I do it with ORDER BY CASE ... END, or do I need to have two querries, and then first check for the value of the parametar, and then, according to that value, call the SELECTs wich sort randomly, or by receiving_time. Mario P.S. The postgres is 8.1.2. -- Mario Splivalo Mob-Art mario.splivalo@mobart.hr "I can do it quick, I can do it cheap, I can do it well. Pick any two."