Re: order by/case expression problem

On Sat, 08 Dec 2001 00:15:31 -0500, Cyber Office wrote:

> The last select statement in the following batch seems to return
> incorrect result.
>
> Am I right?
>
> --------------------------------------------------------------------------
> drop table d
> create table d (d char(10))
> insert into d values ('1')
> insert into d values ('10')
> insert into d values ('9')
>
> select d from d order by d
> d
> ----------
> 1
> 10
> 9
>
> select d from d order by cast(d as numeric)
> d
> ----------
> 1
> 9
> 10
>
> select d from d order by
> case when 1 = 1 then 1 else cast(d as numeric) end
> d
> ----------
> 1
> 10
> 9
>
> select d from d order by
> case when 1 = 1 then d else cast(d as numeric) end
> d
> ----------
> 1
> 9
> 10

Yes. The net result should be the same as in previous statement because 1=1 is always true so your output should be ordered by the column "d". It does work when you use the number of the column in the select list as in the previous example. Good catch and thanks for sharing it with us.

-- 
Mladen Gogala