Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: sorting by 2 columns in one query

    Hi

    In my query, I want to sort the result by 2 columns like:

    by datetime and by another column....I've tried but I can only sort by one using 'order by'.

    Will appreciate any tips.

    Regards
    Sheraz

  2. #2
    Join Date
    Aug 2008
    Posts
    464
    Just wanted to add that I've tried:

    order by datetime, col2 but the result is only ordered by datetime and col2's order gets messed up. Col 2 contains a string like '1.2.3.1.1'. There's only 8 strings like these so I was hoping to get the result so that the datetime is in asc order for 1.2.3.1.1 and then in ascending order for 1.2.3.2.2 and then in ascending order for 1.2.3.3.3. and so on.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'd say that these values are sorted; however, you might not be satisfied with it. Problem is probably in a fact that numbers represented with a string are sorted differently than just numbers. Here's an example:
    Code:
    SQL> with test as
      2    (select '1.2.3' col from dual
      3     union
      4     select '2.1.1' from dual
      5     union
      6     select '12.1.1' from dual
      7    )
      8  select col
      9  from test
     10  order by col;
    
    COL
    ------
    1.2.3
    12.1.1
    2.1.1
    
    SQL>
    Although it is "obvious" that numbers are sorted as "1, 2, 12", string sort puts "12" in front of "2". Therefore, you might need to separate parts of that column (using SUBSTR and INSTR, for example), apply TO_NUMBER to the result and then sort the result.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sounds like you want ORDER BY Col2, datetime
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2008
    Posts
    464

    Thanks

    Didn't realize it was that simple.

    Order by col2, datetime is the answer. Really Appreciated.

Posting Permissions

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