Thread: ORDER BY CASE ...
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+01816579 | +385915912312 |2005-11-23 17:51:06+01816595 | +38598539263 | 2005-11-23 17:58:21+01816594 | +385915929232 | 2005-11-23 17:57:30+01816589| +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+01816579 | +385915912312 |2005-11-23 17:51:06+01816595 | +38598539263 | 2005-11-23 17:58:21+01816594 | +385915929232 | 2005-11-23 17:57:30+01816589| +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."
+-le 13/02/2006 16:35 +0100, Mario Splivalo a dit : | 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. Well, no, in the first, the result of the CASE is 2, and in the second 3, it means that for every line, it'll sort using "2" as value for the first, and "3" for the second query. -- Mathieu Arnold
On Mon, 2006-02-13 at 16:39 +0100, Mathieu Arnold wrote: > | > | > | I tought I'd get differently sorted data, since in the first query I > | said 5=5, and in second I said 5=6. > > Well, no, in the first, the result of the CASE is 2, and in the second 3, it > means that for every line, it'll sort using "2" as value for the first, and > "3" for the second query. > Yes, I realized just a second ago that when ORDER BY is CASED, numbers aren't the column numbers, but the integers itself. 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 -- 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."
On Mon, Feb 13, 2006 at 04:35:30PM +0100, Mario Splivalo wrote: > 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; I'm not sure what you are trying to do here, but it seems that an order by statement should at least contain something that is part of the resultrow. "case when 5=5 then 2 else 3 end desc limit 5" does not contain any column to sort on. So I think it will evaluate to some constant value and not sorting is really done -- __________________________________________________ "Nothing is as subjective as reality" Reinoud van Leeuwen reinoud.v@n.leeuwen.net http://www.xs4all.nl/~reinoud __________________________________________________
+-le 13/02/2006 16:47 +0100, Mario Splivalo a dit : | On Mon, 2006-02-13 at 16:39 +0100, Mathieu Arnold wrote: |> | |> | I tought I'd get differently sorted data, since in the first query I |> | said 5=5, and in second I said 5=6. |> |> Well, no, in the first, the result of the CASE is 2, and in the second 3, |> it means that for every line, it'll sort using "2" as value for the first, |> and "3" for the second query. |> | | Yes, I realized just a second ago that when ORDER BY is CASED, numbers | aren't the column numbers, but the integers itself. | | 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 -- Mathieu Arnold
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
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.