Home » SQL & PL/SQL » SQL & PL/SQL » how to use ORDER BY clause in VARCHAR2 datatype column?
how to use ORDER BY clause in VARCHAR2 datatype column? [message #267324] Thu, 13 September 2007 01:02 Go to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
Hi
SQl> select equipment_no from equipment_master order by equipment_no;

Equipment_no(varchar2)
----------------------
1
12
111
134
1205
2
245
3
313
aluminium casting
drill machine
power press

I wanted to arrange varchar2 datatype column in accending order.Both number,char should come in accending order.
(i.e I am expecting o/p like this
Equipment_no(varchar2)
----------------------
1
2
3
12
111
134
245
313
1025
aluminium casting
drill machine
power press. is it possible?

by
sbmk_design


[Updated on: Thu, 13 September 2007 01:03]

Report message to a moderator

Re: how to use ORDER BY clause in VARCHAR2 datatype column? [message #267332 is a reply to message #267324] Thu, 13 September 2007 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This question has been asked many times here, please make a search BEFORE posting.

In addition,
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: how to use ORDER BY clause in VARCHAR2 datatype column? [message #267354 is a reply to message #267324] Thu, 13 September 2007 01:46 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production



SQL> SELECT *FROM X;

X1
-------------------------
EOIEIERNEL
1
EIENEOI
21
AZEKEKEMROE
234

6 rows selected.

SQL> SELECT *FROM X
2 ORDER BY X1;

X1
-------------------------
1
21
234
AZEKEKEMROE
EIENEOI
EOIEIERNEL

6 rows selected.

SQL> SELECT *FROM X
2 ORDER BY X1 DESC;

X1
-------------------------
EOIEIERNEL
EIENEOI
AZEKEKEMROE
234
21
1

6 rows selected.

Thani....
Re: how to use ORDER BY clause in VARCHAR2 datatype column? [message #267357 is a reply to message #267354] Thu, 13 September 2007 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thani, do you think this is formatted?

And do you think you get the correct order with OP's data?

Regards
Michel

[Updated on: Thu, 13 September 2007 01:50]

Report message to a moderator

Re: how to use ORDER BY clause in VARCHAR2 datatype column? [message #267358 is a reply to message #267324] Thu, 13 September 2007 01:52 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Hai Thani insert '7' and then see the result after order by. Your solution is not requirement.
Re: how to use ORDER BY clause in VARCHAR2 datatype column? [message #267374 is a reply to message #267357] Thu, 13 September 2007 02:19 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Sorry Michel

My Solution is Wrong


SQL> SELECT *FROM X
2 ORDER BY X1 DESC;

X1
----------------------
EOIEIERNEL
EIENEOI
AZEKEKEMROE
7
234
21
1

7 rows selected.

I Will Try

Regards,
Thani...


Re: how to use ORDER BY clause in VARCHAR2 datatype column? [message #267642 is a reply to message #267374] Fri, 14 September 2007 01:32 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
does this help?
scott@test>desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 X                                                  VARCHAR2(10)

scott@test>select * from test;

X
----------
1
100
2
9
999
alum
brass

scott@test>select x from test order by lpad(x,10,' ');

X
----------
1
2
9
100
999
alum
brass

7 rows selected.

scott@test>insert into test values('200');

1 row created.

scott@test>select x from test order by lpad(x,10,' ');

X
----------
1
2
9
100
200
999
alum
brass

8 rows selected.

scott@test>



I still feel it may not work in all cases.

[Updated on: Fri, 14 September 2007 01:34]

Report message to a moderator

Re: how to use ORDER BY clause in VARCHAR2 datatype column? [message #272980 is a reply to message #267332] Mon, 08 October 2007 08:12 Go to previous message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
Hi Mr.Michel / bonker
My Oracle version -- 10.1.0.2
My OS --windows 2003

sorry, I could not find any realted topics
in your search page can i have the link

Note
---- My doubt is can we sort varchar2 datatype
column as i asked above, using sql query ?

by
sbmk_design

[Updated on: Mon, 08 October 2007 08:13]

Report message to a moderator

Previous Topic: Employee Supervisor problem
Next Topic: ANALYZE INDEX
Goto Forum:
  


Current Time: Fri Dec 02 16:42:48 CST 2016

Total time taken to generate the page: 0.22824 seconds