sorting by 2 columns in one query

shajju · #1 (**permalink**) 07-01-09, 04:46

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

shajju · #2 (**permalink**) 07-01-09, 04:58

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.

Littlefoot · #3 (**permalink**) 07-01-09, 05:13

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.