Sorting order in a query 1

[ecojohnson]

I use the following logic for one of my queries:

SELECT Number
FROM DB
ORDER BY Number ASC

However, if my numbers are 1-10, it comes out in the following order:

1,10,2,3,4,5,6,7,8,9

Is there a simple way to make this order go logically (i.e. 1,2,3,4,5,6,7,8,9,10). I'm sure I can come up with a way to do it (using a LEFT function or something), but I wasn't sure if there was some quick and easy way to do so.

I apologize if this is an extemely simple question, buy my SQL book does not touch on this at all. Thanks.

 

[Silvano]

in the database you have to specify data type of the number field in question; if your output is 1,10,2..., data type of that field must be text; change it to number and than your output should be 1,2,3...8,9,10 Sylvano
dsylvano@hotmail.com

"every and each day when I learn something new is a small victory..."

 

[ecojohnson]

Unfortunately, it has to be a text field. I didn't specify that. I apologize.

I guess I could just create some kind of logic to first sort by the last number, and then by the first number (or something along that line).

If you have any better ideas, please let me know. Thanks!

 

[Silvano]

i think that it would be better for you to reverse the logic and instead of sorting the query output, try sorting the input into database;
what i have in mind is that first, limit database field to 10 characters (this is just an example), than, write a small procedure that will input your number into database field with all leading blanks (" &quot before the acctual number;
for example, if your input is 8, the number will be written as " 8" (9 blanks and 8);
in this case query output will be 1,2,3...8,9,10; now you can play just with removing blanks if necessary;

hope this helps Sylvano
dsylvano@hotmail.com

"every and each day when I learn something new is a small victory..."

 

[ecojohnson]

Hmmm. Good idea. I will give it a try.

Thanks!

 

[strantheman]

hi ecojohnson

I just want to make sure I understand. You have a column in a table and it contains numbers, but the field type is text? You need to get it into another column of type integer.

What DB software are you using?

 

[ecojohnson]

The reason why it is a text (actually VARCHAR) is because there is a remote chance that the value can be non-numeric. I am using ORACLE.

Because of the chance of this situation, I had to account for non-numeric numbers.

However, I was able to come up with a workable solution using the LPAD function.

 

[rtefft]

Here's a solution that works without changing your current database structure. It will handle leading or trailing spaces in your data, and will handle up to a 10-byte NUMBER value (adjust as needed) This is for Oracle:

SELECT Number
FROM DB
ORDER BY lpad(rtrim(Number),10)


Good luck!

Rich Tefft
SQL & PL/SQL Instructor ____________________________
Rich Tefft
PL/SQL Programmer/Instructor