From: Mario Splivalo <mario.splivalo@mobart.hr>
To: pgsql-sql@postgresql.org
Subject: ORDER BY CASE ...
Date: Mon, 13 Feb 2006 16:35:30 +0100
Message-ID: <1139844930.13054.23.camel@localhost.localdomain> (raw)
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."
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: mario.splivalo@mobart.hr
Subject: Re: ORDER BY CASE ...
In-Reply-To: <1139844930.13054.23.camel@localhost.localdomain>
* 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