Re: ORDER BY CASE ...

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