Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    13

    Unanswered: sorting strings as numbers

    I have a small table "myFields"
    +--------+--------+--------+-----+
    | field1 | field2 | field3 | ... |
    +--------+--------+--------+-----+
    | d1 | ... | aaa | ... |
    +--------+--------+--------+-----+
    | d22 | ... | bbb | ... |
    +--------+--------+--------+-----+
    | d913 | ... | aaa | ... |
    +--------+--------+--------+-----+
    | ... | ... | ... | ... |
    +--------+--------+--------+-----+
    I would like to sort by the numbers in field1.
    My first thought was this statement:
    SELECT SUBSTR(field1,2) AS D_NO, * FROM myFields WHERE ( field3 = 'aaa') ORDER BY 1
    but it gives me errors (ORA-00936: missing expression).
    It tried different variations, like:
    SELECT *,SUBSTR(field1,2) AS D_NO FROM myFields WHERE ( field3 = 'aaa') ORDER BY 2
    wich gives "ORA-00923: FROM keyword not found where expected"

    Suggestions?

    Thanks,
    kromo

  2. #2
    Join Date
    Dec 2003
    Posts
    74
    as far as I know you cannot type

    "select field1, * from table " in oracle what you need to do is

    SELECT SUBSTR(FIELD1,2) D_NO,FIELD2,FIELD3,FIELD4,FIELD5
    FROM TABLE
    WHERE ( FIELD3 = 'AAA') ORDER BY 1

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can use column names and * together, but you must use a table alias on *:

    SELECT SUBSTR(FIELD1,2) D_NO, TABLE.*
    FROM TABLE
    WHERE ( FIELD3 = 'AAA')
    ORDER BY 1;

  4. #4
    Join Date
    Dec 2003
    Posts
    74
    cheers 4 that

  5. #5
    Join Date
    Dec 2003
    Posts
    13
    Thanks a lot,
    kromo

Posting Permissions

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