Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Location
    Pune - Maharastra. India
    Posts
    1

    Red face Unanswered: Oracle sort by gives wrong results

    Hello,

    I have table T1, which has 4 columns. From that first 3 columns used in order by.
    E.g
    create table t1
    ( no1 char(1),
    no2 number(1),
    no3 char(1)
    no4 number(1)
    );
    & inserted rows are - when I fire
    select * from t1 following o/p displayed.
    ---------------------
    No1 No2 No3 No4
    --------------------
    x 1 B 1
    x 1 B 2
    x 1 B 3

    but when I fire -
    select * from t1 order by no2,no1,no3 then o/p is
    ---------------------
    No1 No2 No3 No4
    --------------------
    x 1 B 1
    x 1 B 3
    x 1 B 2

    actually the rows are shuffled - why this is so?

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Because all n01, no2, no3 contain the same info so there is no order guaranteed. If you want to force them in order include no4 at the end of the order. The only way to guarantee order is to use a valid order by clause.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The sorting is exactly as you asked. In both queries you supplied the sort order is the same for for cols, No1, No2, No3. Which is exactly what you asked for.
    You are talking about col No4 not being in sequential order. You did not specify in your query that you wanted to order by that column. If you are asking why No4 is sorted in your first query, but not in your second, it is because your table has the rows in one order and your index(used for the sorting) has them in another order.
    Dave

Posting Permissions

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