Thread: ORDER BY CASE ...

ORDER BY CASE ...

From
Mario Splivalo
Date:
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."




Re: ORDER BY CASE ...

From
Mathieu Arnold
Date:

+-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


Re: ORDER BY CASE ...

From
Mario Splivalo
Date:
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."




Re: ORDER BY CASE ...

From
Reinoud van Leeuwen
Date:
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
__________________________________________________


Re: ORDER BY CASE ...

From
Mathieu Arnold
Date:
+-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


Re: ORDER BY CASE ...

From
Mario Splivalo
Date:
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



Re: ORDER BY CASE ...

From
Bruno Wolff III
Date:
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.