order by/case expression problem

From: Cyber Office <jcheong_at_cooper.com.hk>
Date: 8 Dec 2001 05:15:31 GMT
Message-ID: <9us7lj$2qb1@imsp212.netvigator.com>

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

-- 
http://www-902.ibm.com/hk/sme/corner/solution_detail.html#a3
http://www.attunity.com

Received on Fri Dec 07 2001 - 23:15:31 CST