Order by SQL Statement not working.?

Question

Hello, im trying to sort a text field that contains both text and numbers numerically. I have

"SELECT * FROM PRODUCTS WHERE PRO_TYPE = TYPE_ID ORDER BY PRO_NAME ASC"

My products that are displaying display as....

1, 11, 12, 2, 21, 22.... Instead of , 1, 2, 3, 4, ....

Any ideas?

Im coding in ASP (vb) If this helps anyone. Also, yes, the Access database field is a text field. Which to my understanding must stay a text field to hold the full name of the product. For example... i need the product name to display like.... 4.5 oz. Jar, 6 oz. Jar, 25 oz. Jar and so forth...

yes, a VARCHAR

Answer 1

This is happening because your pro_name field is a string, and it is sorting alphabetically, whereas you want to treat it as a number, and sort it numerically.

If you want to order by the pro_name field numerically, you need to convert the numeric portion of the string to a number. Use the cast statement for this:

select a, b, c...
from table
order by cast(prod_name as integer)

If only the first portion of the prod_name is a number, and the remainder is alpha-text, the cast formula won't work unless you extract the numeric portion from the string using patindex, and looking for a consistent delimiter.

IM me if you need help.

Answer 2

This is a result of the field beinga text field and having other text in it. You can sort numbers in the numeric format but if you are wokring with text you either sort them in textual order (1, 11, 12, 13, 2, 21, 22....) or you have to use a conversion to numeric during the select. Since you have both text and numbers in the field the latter step would fail when it hits the text.

Answer 3

Not exactly sure because of your wording... "contains TEXT and numbers numerically"?

In COBOL, before executing a similar SQL statement, you would need to reformat numerical data stored in a text-type field by moving a legitimate numerical value into a numeric field. This will right-justify, as opposed to left, and pad with zeros, making '1' an '01', etc, so that '02' and '03' would come before '11'.

I guess what I am saying is basically your existing data type does not support the order you are trying to do.

One final note, it would be helpful if you identified the language you are coding in, such as "COBOL for DB2".

Good luck with your problem.

Answer 4

is your PRO_NAME field a VARCHAR or an INTEGER, etc.?

1, 11, 12, 2... is correct sorting if sorting alphabetically (what happens on VARCHAR fields). The only way to get around this, if you need to include non-numeric values, is to include leading 0s on these fields (01, 02, 11, 12...).

Answer 5

It's sorting by alphabetical order on the product name not by number order on the Type_id.

Answer 6

Do you know for a fact that the PRO_NAME column will always be a number, albeit stored as a varchar?

If so, why don't you just cast it?

SELECT * FROM Products ORDER BY CINT(Pro_Name) ASC

Answer 7

Your text filed is sorted correclty - it is in alphabetical order.

Either change the field type (probaly a big pain at this point) or ...maybe... you can do something to make it numeric like

ORDER BY (0 + PRO_NAME) ASC
or with leading zeroes so '0002' comes before '0011'
ORDER BY LPAD(PRO_NAME,6,'0') ASC

Answer 8

probably the are listed orderel alfabeticaly on the name of the product ; that numbers are product id .
so if you have product id no 1 with name "Gilbert paper" and product id no 5 with name "Albert paper" the display will list like tihis:

5. Albert Paper
1. Gilbert paper
as you can se they are ordered by name ascendent a s your request was

if you want them to list 1,2,3,4 on prod id then you must ask :

"SELECT * FROM PRODUCTS WHERE PRO_TYPE = TYPE_ID ORDER BY PRO_ID ASC"