Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    15

    Unanswered: Sorting Decimals

    Hi,

    I am trying to display the codes in a table. The codes are as follows:
    1.1
    1.2
    1.3
    1.4
    1.5
    1.6
    1.7a
    1.7b
    1.8
    1.9
    1.10
    .
    .
    .

    This is the way they are stored in the table. But when retrieved on the form they appear in the following order
    1.1
    1.10
    1.2
    1.3
    1.4
    .
    .
    .
    .
    The datatype of this field is Varchar2.

    Is there a way to sort them properly so that the code 1.10 comes after 1.9
    Please help. This is the only problem(As of now) that is keeping me from completing my project.
    I am using Oracle 9i.

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Sorting on a varchar column will have this behaviour - it is working exactly as it should. A string(=varchar) sort (basically - but there are exceptions) does a sort based on the left to right ASCII value of each character.

    A sort on a column of type number, does a mathematical numeric sort.

    Unfortunately, your column combines alpha chars as part of the number, the result of which is stored as a varchar. Regardless of the the alpha chars, any sort on numbers stored in a varchar column will cause this. You can't use to the TO_NUMBER() function because the alpha chars would cause it to fail.

    Without writing yourself a function to determine the actual numeric value (or some smart inline SQL to do the same), the column is not truly sortable. Even it were - image when numbers such as 8.452e4 start getting stored there.

    You have a fundamental design problem with your data model.

    Regards
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Sep 2003
    Posts
    15
    Thank you for ur reply. As u said I shall work on my design and see if i can come up with another way of doing this.

    Thanks once again


    Originally posted by billm
    Hi,

    Sorting on a varchar column will have this behaviour - it is working exactly as it should. A string(=varchar) sort (basically - but there are exceptions) does a sort based on the left to right ASCII value of each character.

    A sort on a column of type number, does a mathematical numeric sort.

    Unfortunately, your column combines alpha chars as part of the number, the result of which is stored as a varchar. Regardless of the the alpha chars, any sort on numbers stored in a varchar column will cause this. You can't use to the TO_NUMBER() function because the alpha chars would cause it to fail.

    Without writing yourself a function to determine the actual numeric value (or some smart inline SQL to do the same), the column is not truly sortable. Even it were - image when numbers such as 8.452e4 start getting stored there.

    You have a fundamental design problem with your data model.

    Regards
    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •