From: | Mathieu Arnold <mat(at)mat(dot)cc> |
---|---|
To: | mario(dot)splivalo(at)mobart(dot)hr, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: ORDER BY CASE ... |
Date: | 2006-02-13 15:39:12 |
Message-ID: | 9693350566E722C466358E08@[192.168.9.47] |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
+-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
From | Date | Subject | |
---|---|---|---|
Next Message | Reinoud van Leeuwen | 2006-02-13 15:40:24 | Re: ORDER BY CASE ... |
Previous Message | Mario Splivalo | 2006-02-13 15:35:30 | ORDER BY CASE ... |