Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Order By in Query?

 
vijay saraf
Ranch Hand
Posts: 141
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello All,

I know its Java forum,
but Java ranch has solved my lot of problem so i even for SQL i am posting my question on Ranch.

Here i have column of type varchar in a table of SQL 2000.....
the problem begins with order by queries.......
the column usually have data like
A) 1,2,3,4,5,10,1F,2F........
when i put order by in my query the result comes as
B) 1,10,1F,2,2F,3,4,5.............

i wanted to maintain the order as in (A).....
If any one have any idea please help me.

Thanks,
Vijay Saraf.
[ June 18, 2007: Message edited by: Bear Bibeault ]
 
Edisandro Bessa
Ranch Hand
Posts: 584
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey Man !

Try this one :

select column_name from table
order by char_length(column_name), column_name

It fits as a glove.
 
vijay saraf
Ranch Hand
Posts: 141
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well Thanks Edisandro,

I have tried your solution but I am getting error as
'CHAR_LENGTH' is not a recognized function name
. I think in SQL 2000 this function doesn't work.
If you have any other solution please share with me.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
char_length is a Sybase function isn't it? Sorting of text fields in SQL Server is done based on the collation you use. You can either specify the collation when you create the database, in which case the sorting rules are applied to every text field, or you can specify the collation you want to use as part of the sql statement:

Of the top of my head I can't think of a collation that sorts as you want, but this page lists them.
 
vijay saraf
Ranch Hand
Posts: 141
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok....Paul
But That's long way..and needed table alter....
i was in search of any trick that i can put in query.....
any way ......
One Solution i have found .....but it is too not working correctly
i am still looking for solution....

[ June 14, 2007: Message edited by: vijay saraf ]
[ June 14, 2007: Message edited by: vijay saraf ]
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Re-read my post vijay saraf. No DDL is required, you can specify the collation in the order by clause of your query, so you can apply different sorting rules in an ah-doc manner.
 
vijay saraf
Ranch Hand
Posts: 141
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok.

But i have not ever use collation.
let me search on web.if still i face the problem i will come back.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you use SQL Server at all, you are using collations, so they are a good thing to learn about if you are doing much work with this DB. It's worth remembering though that if your application relies on this type of ordering you make your code database dependent, so you could also check to see if you can return your results unordered and order them in your applciation.
 
Jan Cumps
Bartender
Posts: 2597
12
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
try
Regards, Jan
 
vijay saraf
Ranch Hand
Posts: 141
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Jan,

I have tried your code. but i was getting exceptio as
Invalid Object.

Then i search on the net and from Page i got the function as bellow ;


This is working But result comes as in the following order
1F,2F,1.2.....9,10,.....20,21,21A,21B......
I am little bit confused why some alphanumerics are aprearing at front and some at rear.
As i am not master in Database coding this code looks like " ant on paper " to me.
If you can suggest some thing to please help.
 
Jan Cumps
Bartender
Posts: 2597
12
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your example data contained only hexadecimal values (1,2,3,4,5,10,1F,2F..). That's why I proposed to use a method that converts them to decimal, and sort on that.

If your column does not contain hexadecimals, you will have to to split your column value in two parts, the numerical part and the alpha part (maybe by creating two stored functions getNum() and getAlpha()).
First sort on the numerical part, then sort on the alpha part.

Regards, Jan
 
vijay saraf
Ranch Hand
Posts: 141
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK Thanks Jan,
I will exercise for that.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic