CODEWITHSUNDEEP
sqloracle-database
Cengiz Dogan
Cengiz Dogan

Reputation: 149

why Order by does not sort?

I have a query below, I want it to sort the data by id, but it doesn't sort at all.

Select distinct ec.category,ec.id
from print ec
order by ec.id asc

What could be the reason?

this is the output :

enter image description here

Upvotes: 0

Views: 1254

Answers (5)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

The difference lies in the way varchar and number are sorted. in your case, since you have used varchar data type to store number, the sorting is done for the ASCII values.

NUMBERS when sorted as STRING

SQL> WITH DATA AS(
  2  SELECT LEVEL rn FROM dual CONNECT BY LEVEL < = 11
  3  )
  4  SELECT rn, ascii(rn) FROM DATA
  5  order by ascii(rn)
  6  /

        RN  ASCII(RN)
---------- ----------
         1         49
        11         49
        10         49
         2         50
         3         51
         4         52
         5         53
         6         54
         7         55
         8         56
         9         57

11 rows selected.

SQL>

As you can see, the sorting is based on the ASCII values.

NUMBER when sorted as a NUMBER itself

SQL> WITH DATA AS(
  2  SELECT LEVEL rn FROM dual CONNECT BY LEVEL < = 11
  3  )
  4  SELECT rn, ascii(rn) FROM DATA
  5  ORDER BY rn
  6  /

        RN  ASCII(RN)
---------- ----------
         1         49
         2         50
         3         51
         4         52
         5         53
         6         54
         7         55
         8         56
         9         57
        10         49
        11         49

11 rows selected.

SQL>

How to fix the issue?

Change the data type to NUMBER. As a workaround, you could use to_number.

Using to_number -

SQL> WITH DATA AS(
  2  SELECT to_char(LEVEL) rn FROM dual CONNECT BY LEVEL < = 11
  3  )
  4  SELECT rn, ascii(rn) FROM DATA
  5  ORDER BY to_number(rn)
  6  /

RN   ASCII(RN)
--- ----------
1           49
2           50
3           51
4           52
5           53
6           54
7           55
8           56
9           57
10          49
11          49

11 rows selected.

SQL>

Upvotes: 2

Beginner
Beginner

Reputation: 4153

You can only just cast/convert the datatype in a query

Select distinct ec.category,ec.id from print ec order by cast(ec.id as int) asc

Upvotes: 0

Venkataraghavan Yanamandram
Venkataraghavan Yanamandram

Reputation: 431

when you sort a string datatype that has in values it produce result as

1

10

11

2

21 etc...

Hence

Change your Id datatype to int/bigint

Upvotes: 0

Pieter De Clercq
Pieter De Clercq

Reputation: 1971

Make sure the type of your "id" column is int. (integer = number)

Right now it is probably text, char or varchar(for text, strings).

You can't sort numbers alphabetically or strings/text chronologically like you are trying now.

Upvotes: 0

Patrick Hofman
Patrick Hofman

Reputation: 156948

Looking at your data, the column data type is a varchar, aka 'text'.

If it is text, it sorts like text, according to the place the character occurs in the character set used.

So each column is ordered on the first character, then the second, etc. So 2 comes after 11.

Either make the column a numeric data type, like number, or use to_number in the sorting:

select distinct ec.category,ec.id
from print ec
order by to_number(ec.id)

Upvotes: 7

Related Questions