Home » SQL & PL/SQL » SQL & PL/SQL » Sort alphanumberic column (Oracle 10g)
Sort alphanumberic column [message #387780] Fri, 20 February 2009 10:27 Go to next message
balji_dxb
Messages: 37
Registered: October 2007
Member
SELECT * FROM SORT_TEST
order by 1

1
10
A1
20
2

the output should be

1
2
10
20
A1

i tried
SELECT a
FROM SORT_TEST
ORDER BY to_number(regexp_substr(A,'^0-9+')),A but the output that i get is

1
10
2
20
A1

which is wrong

how do i sort the way i want
Re: Sort alphanumberic column [message #387781 is a reply to message #387780] Fri, 20 February 2009 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From one of your previous post:
Michel Cadot wrote on Tue, 02 October 2007 06:30
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

Alway post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel


Re: Sort alphanumberic column [message #387783 is a reply to message #387780] Fri, 20 February 2009 10:45 Go to previous messageGo to next message
balji_dxb
Messages: 37
Registered: October 2007
Member
I am using oracle 10g.

Table structure is
create table sort_test
(a varchar220)
Re: Sort alphanumberic column [message #387787 is a reply to message #387783] Fri, 20 February 2009 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Answer as soon as you follow the guide.

Regards
Michel
Re: Sort alphanumberic column [message #387788 is a reply to message #387787] Fri, 20 February 2009 11:09 Go to previous messageGo to next message
balji_dxb
Messages: 37
Registered: October 2007
Member
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit.


Hi Mikel,
can you please guide me, i dont know what i am violating from the guide

Re: Sort alphanumberic column [message #387789 is a reply to message #387788] Fri, 20 February 2009 11:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 20 February 2009 17:33
From one of your previous post:
Michel Cadot wrote on Tue, 02 October 2007 06:30
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

Alway post a Test case: create table and insert statements along with the result you want with these data.
Regards
Michel

[Updated on: Fri, 20 February 2009 11:12]

Report message to a moderator

Re: Sort alphanumberic column [message #387790 is a reply to message #387780] Fri, 20 February 2009 11:27 Go to previous messageGo to next message
balji_dxb
Messages: 37
Registered: October 2007
Member
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit

SQL> SELECT * FROM SORT_TEST
2 /

A
----------
1
10
A1
20
2
3
30

SQL> SELECT a
2 FROM SORT_TEST
3 ORDER BY to_number(regexp_substr(A,'^0-9+')),A
4 /

A
----------
1
10
2
20
3
30
A1

7 rows selected.

SQL> desc sort_test
Name Null? Type
----------------------------------------- -------- ----------------------------
A VARCHAR2(10)

the output should be
1
2
3
10
20
30
A1
Re: Sort alphanumberic column [message #387791 is a reply to message #387790] Fri, 20 February 2009 11:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you should first read.

Regards
Michel
Re: Sort alphanumberic column [message #387794 is a reply to message #387780] Fri, 20 February 2009 12:03 Go to previous messageGo to next message
balji_dxb
Messages: 37
Registered: October 2007
Member
I read it and i dont know how can i put my question, i think the question is understandable, let me know what is wrong in this . please help
Re: Sort alphanumberic column [message #387801 is a reply to message #387794] Fri, 20 February 2009 12:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 20 February 2009 18:12
Michel Cadot wrote on Fri, 20 February 2009 17:33
From one of your previous post:
Michel Cadot wrote on Tue, 02 October 2007 06:30
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

Alway post a Test case: create table and insert statements along with the result you want with these data.
Regards
Michel



Use code tags as explained in the guide.
Re: Sort alphanumberic column [message #387802 is a reply to message #387780] Fri, 20 February 2009 12:16 Go to previous messageGo to next message
balji_dxb
Messages: 37
Registered: October 2007
Member
select a
from sort_test
order by regexp_replace(POR.LINE_SEQ_NO,'[url=/wiki/:digit:]:digit:[/url]') nulls first,
to_number(regexp_substr(POR.LINE_SEQ_NO,'[url=/wiki/:digit:]:digit:[/url]+'))
.


Resolved my issue. anyway thans

[Updated on: Fri, 20 February 2009 12:33] by Moderator

Report message to a moderator

Re: Sort alphanumberic column [message #387812 is a reply to message #387802] Fri, 20 February 2009 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time you will have a far most faster answer if you follow the guide; it is not so hard most of the posters succeed in this.

Regards
Michel
Re: Sort alphanumberic column [message #387897 is a reply to message #387812] Sat, 21 February 2009 10:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Fri, 20 February 2009 19:45
Next time you will have a far most faster answer if you follow the guide; it is not so hard most of the posters succeed in this.

Regards
Michel


It is also not so hard to create a one column table and insert 5 rows without the original poster providing a testcase.
Single column results are also very well readable without code-tags.
Re: Sort alphanumberic column [message #387899 is a reply to message #387897] Sat, 21 February 2009 10:55 Go to previous messageGo to next message
balji_dxb
Messages: 37
Registered: October 2007
Member
Thank you frank
Re: Sort alphanumberic column [message #387900 is a reply to message #387897] Sat, 21 February 2009 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It is also not so hard to create a one column table and insert 5 rows without the original poster providing a testcase.

If it is not why didn't he provide it? If he does not provide it for a simple case, will he provide it for a more complex one next time?

Quote:
Single column results are also very well readable without code-tags.

If he does not do it for a simple one column result, will he for a multiple one if we don't say to do it?

Actually he seems to not be able to do it, in any case.

Regards
Michel

[Updated on: Sat, 21 February 2009 10:57]

Report message to a moderator

Re: Sort alphanumberic column [message #387902 is a reply to message #387900] Sat, 21 February 2009 11:01 Go to previous messageGo to next message
balji_dxb
Messages: 37
Registered: October 2007
Member
I have shown the table structure (desc sort_test) and i can insert the values through any means(toad, application etc). so not necessary i should write a insert statement, that is why i gave the values that is available in the table (select a from sort_test).
Re: Sort alphanumberic column [message #387903 is a reply to message #387899] Sat, 21 February 2009 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
balji_dxb wrote on Sat, 21 February 2009 17:55
Thank you frank

So you are now consolidated in your behaviour to not follow the guide.
Thank you Frank.

Regards
Michel

Re: Sort alphanumberic column [message #387904 is a reply to message #387902] Sat, 21 February 2009 11:02 Go to previous messageGo to next message
balji_dxb
Messages: 37
Registered: October 2007
Member
and infact the solution that i mentioned i gave within code tags and it is displaying something different than what i typed. so i dont feel i made any mistake, instead of wasting time on this
Re: Sort alphanumberic column [message #387905 is a reply to message #387902] Sat, 21 February 2009 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
so not necessary i should write a insert statement

For you no, but for us to help you, yes.
Don't you think we want to test what we think to post you instead of posting wrong solution?
What I ask is just TO HELP YOU.

Regards
Michel
Re: Sort alphanumberic column [message #387906 is a reply to message #387903] Sat, 21 February 2009 11:04 Go to previous messageGo to next message
balji_dxb
Messages: 37
Registered: October 2007
Member
Michel, Please take it as a feedback and dont take it as a critism we are not going to get anything in it. we are here to help and get help
Re: Sort alphanumberic column [message #387907 is a reply to message #387904] Sat, 21 February 2009 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
balji_dxb wrote on Sat, 21 February 2009 18:02
and infact the solution that i mentioned i gave within code tags and it is displaying something different than what i typed. so i dont feel i made any mistake, instead of wasting time on this

And we have to waste ours to write the test case to provide you help?

Regards
Michel

Re: Sort alphanumberic column [message #387908 is a reply to message #387906] Sat, 21 February 2009 11:05 Go to previous messageGo to next message
balji_dxb
Messages: 37
Registered: October 2007
Member
For you no, but for us to help you, yes. 
Don't you think we want to test what we think to post you instead of posting wrong solution?


i didnt write insert statement to insert values, then how can i give the insert statement
Re: Sort alphanumberic column [message #387909 is a reply to message #387906] Sat, 21 February 2009 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
balji_dxb wrote on Sat, 21 February 2009 18:04
Michel, Please take it as a feedback and dont take it as a critism we are not going to get anything in it. we are here to help and get help

But you don't want to make the effort to help us help you.

Regards
Michel

Re: Sort alphanumberic column [message #387910 is a reply to message #387908] Sat, 21 February 2009 11:06 Go to previous message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
balji_dxb wrote on Sat, 21 February 2009 18:05
For you no, but for us to help you, yes. 
Don't you think we want to test what we think to post you instead of posting wrong solution?


i didnt write insert statement to insert values, then how can i give the insert statement

The way we have to: type on your keyboard.

Regards
Michel

Previous Topic: Error access remote table using synonyms
Next Topic: ORA-00942
Goto Forum:
  


Current Time: Wed Dec 07 02:52:53 CST 2016

Total time taken to generate the page: 0.07337 seconds