ansaurus

Question

I am unable to sort data in ascending order using ORDER BY ASC

Answer 1

+11 A:

The column type must be a numeric (int, number, bigint, etc) type..

Looks like right now it is a VARCHAR type column... which is sorted like a dictionary...

Garis Suero 2010-08-20 03:27:15

if you cannot change the column type, you can order by to_number(account_id) (which is maybe quite slow)

Thilo 2010-08-20 03:28:33

yes its varchar but why is it behaving like this can't we sort column with type varchar

farkhunda 2010-08-20 03:28:33

@farkhunda. If you sort varchar, it sorts as strings (not numbers), and the output you show is the result

Thilo 2010-08-20 03:29:54

@farkhunda: when ordering character strings, the values are not treated as integers - they are treated as character strings and ordered as character strings (just as shown). This was a reason why leading zeroes were popular in the dark ages of computing - alphabetic sorts sorted numbers with leading zeroes in the correct numeric order too.

Jonathan Leffler 2010-08-20 03:32:25

@farkhunda: Strings sort character by character. The character `'2'` comes before `'6'`. So `'1002<whatever else the string contains, numbers letters what not'` is going to always come before `'1006'` If you want numeric sorting you will have to convert the strings to numbers before the sort. (Unless some of the strings aren't numbers, that would blow up then.)

Shannon Severance 2010-08-20 03:35:44

Thanks everyone problem solved. I changed the query toselect accountid from general order by CONVERT(INT,accountid) ASC

farkhunda 2010-08-20 03:36:07

If it is a new App or a new DB design, the best you can do is change the column type, CONVERT in a large amount of data could slowdown your query.

Garis Suero 2010-08-20 03:49:56

ansaurus

tags:

views:

answers:

I am unable to sort data in ascending order using ORDER BY ASC

related questions