CODEWITHSUNDEEP
sqloracle11g
user3476679
user3476679

Reputation: 53

Using Order By in Oracle 11g but the list is not orderred

I have an OrderId column and the values are a mix of single digits like 1,2,3 to 4 digits like 3456. When I query the table and use Order By OrderId the list is not ordered by OrderId the way I want.

So Instead of showing: 1,8,10,20,70,111,2345,4567

it is showing : 1,111,20,2345,4567,70,8 etc

Is there a way to order this Ids properly? my simple query is like this:

select * from a table
order by Orderid

Upvotes: 1

Views: 41

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

If, for some reason, converting to a number doesn't work, there is another trick. You can sort first by the length and then by the value:

order by length(OrderId), OrderId

Upvotes: 0

radar
radar

Reputation: 13425

This is doing lexicographic sort, that means the column type is varchar2

you need to convert the varchar2 to number and do order by

select * from Table
order by TO_NUMBER(Orderid)

Upvotes: 2

Related Questions