aspose file tools*
The moose likes JDBC and the fly likes Order By in Query? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Order By in Query?" Watch "Order By in Query?" New topic
Author

Order By in Query?

vijay saraf
Ranch Hand

Joined: Jan 08, 2005
Posts: 141
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 ]

Thanks
Vijay Saraf.
Edisandro Bessa
Ranch Hand

Joined: Jan 19, 2006
Posts: 584
Hey Man !

Try this one :

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

It fits as a glove.


"If someone asks you to do something you don't know how to, don't tell I don't know, tell I can learn instead." - Myself
vijay saraf
Ranch Hand

Joined: Jan 08, 2005
Posts: 141
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

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
vijay saraf
Ranch Hand

Joined: Jan 08, 2005
Posts: 141
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

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Jan 08, 2005
Posts: 141
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

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Dec 20, 2006
Posts: 2500
    
    8

try
Regards, Jan


OCUP UML fundamental and ITIL foundation
youtube channel
vijay saraf
Ranch Hand

Joined: Jan 08, 2005
Posts: 141
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

Joined: Dec 20, 2006
Posts: 2500
    
    8

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

Joined: Jan 08, 2005
Posts: 141
OK Thanks Jan,
I will exercise for that.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Order By in Query?