This discussion is archived
5 Replies Latest reply: Feb 10, 2013 12:40 PM by Frank Kulash RSS

ORDER BY clause with expression

990239 Newbie
Currently Being Moderated
Considering that all the column names and table name is valid, what happens internally when the following query is run?

SQL> SELECT STU_ID, STU_NAME FROM STUDENT ORDER BY 2+3, STU_ID;

What i found out was the rows get ordered in ascending order of STU_ID. It's quite clear that it is ignoring the expressing 2+3, so my doubts are:

(a) Is it appending any pseudo-column to the table with 2+3 getting evaluated to 5?

(b) If there exist a 5th column in the table student and we specify it in the select statement's select list (5 column names) then why its not considering it as 5 and sorting the data according that?

(c) Is it true that, any expression we write is getting evaluated to NULL? Then is it that ORDER BY NULL evaluates to no ordering and the parser searches for next column-name or position (if specified) ?

(d) if neither then, what's the reason of the ignorance?
  • 1. Re: ORDER BY clause with expression
    Solomon Yakobson Guru
    Currently Being Moderated
    (a), but it is not appending, just calculating.

    SY.
  • 2. Re: ORDER BY clause with expression
    Solomon Yakobson Guru
    Currently Being Moderated
    It is all documented: ORDER BY.

    SY.
  • 3. Re: ORDER BY clause with expression
    damorgan Oracle ACE Director
    Currently Being Moderated
    What you are trying to do appears to be programmatic nonsense but there are a couple of ways to write a dynamic WHERE clause in addition to the use of Native Dynamic SQL. Here are two examples from Morgan's Library (http://www.morganslibrary.org/library.html).

    The first uses DECODE and you could alternatively use CASE.
    SELECT 'ALTER ' || object_type || ' '|| object_name ||' COMPILE;'
    FROM user_objects
    WHERE object_type IN ('FUNCTION', 'PACKAGE', 'PROCEDURE', 'TRIGGER', 'VIEW')
    ORDER BY DECODE(object_type, 'VIEW','A', 'FUNCTION','B', 'PROCEDURE', 'C', 'PACKAGE','D', 'Z');
    The second a SELECT statement and you could craft your own.
    SELECT DECODE((SELECT 'x' FROM DUAL), ( SELECT 'x' FROM DUAL), (SELECT 'Morgan' FROM dual)) AS RESULT
    FROM (SELECT 'm' FROM dual)
    WHERE (SELECT 1 FROM dual) = (SELECT 1 FROM dual)
    AND (SELECT 2 FROM dual) BETWEEN (SELECT 1 FROM dual) AND (SELECT 3 FROM dual)
    AND NVL((SELECT NULL FROM dual ), (SELECT 'z' FROM dual)) = (SELECT 'z' FROM dual)
    ORDER BY (SELECT 1 FROM dual);
  • 4. Re: ORDER BY clause with expression
    Solomon Yakobson Guru
    Currently Being Moderated
    Solomon Yakobson wrote:
    It is all documented: ORDER BY.
    Actually, documentation could be better:

    position Specify position to order rows based on their value for the expression in this position of the select list. The position value must be an integer.

    First of all, it should say position must be a literal. Secondly, it can be any numeric literal. It looks like Oracle truncates it to a whole number:
    SQL> select job,ename from emp order by 1.2
      2  /
    
    JOB       ENAME
    --------- ----------
    ANALYST   SCOTT
    ANALYST   FORD
    CLERK     MILLER
    CLERK     JAMES
    CLERK     SMITH
    CLERK     ADAMS
    MANAGER   BLAKE
    MANAGER   JONES
    MANAGER   CLARK
    PRESIDENT KING
    SALESMAN  TURNER
    
    JOB       ENAME
    --------- ----------
    SALESMAN  MARTIN
    SALESMAN  WARD
    SALESMAN  ALLEN
    
    14 rows selected.
    
    SQL> select job,ename from emp order by 1.9
      2  /
    
    JOB       ENAME
    --------- ----------
    ANALYST   SCOTT
    ANALYST   FORD
    CLERK     MILLER
    CLERK     JAMES
    CLERK     SMITH
    CLERK     ADAMS
    MANAGER   BLAKE
    MANAGER   JONES
    MANAGER   CLARK
    PRESIDENT KING
    SALESMAN  TURNER
    
    JOB       ENAME
    --------- ----------
    SALESMAN  MARTIN
    SALESMAN  WARD
    SALESMAN  ALLEN
    
    14 rows selected.
    
    SQL> select job,ename from emp order by 2.3
      2  /
    
    JOB       ENAME
    --------- ----------
    CLERK     ADAMS
    SALESMAN  ALLEN
    MANAGER   BLAKE
    MANAGER   CLARK
    ANALYST   FORD
    CLERK     JAMES
    MANAGER   JONES
    PRESIDENT KING
    SALESMAN  MARTIN
    CLERK     MILLER
    ANALYST   SCOTT
    
    JOB       ENAME
    --------- ----------
    CLERK     SMITH
    SALESMAN  TURNER
    SALESMAN  WARD
    
    14 rows selected.
    
    SQL>
    SY.
  • 5. Re: ORDER BY clause with expression
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!
    987236 wrote:
    Considering that all the column names and table name is valid, what happens internally when the following query is run?

    SQL> SELECT STU_ID, STU_NAME FROM STUDENT ORDER BY 2+3, STU_ID;

    What i found out was the rows get ordered in ascending order of STU_ID. It's quite clear that it is ignoring the expressing 2+3, so my doubts are:

    (a) Is it appending any pseudo-column to the table with 2+3 getting evaluated to 5
    There's no pseduo-column involved. (Pseudo-columns are something that Oracle provides for you, without you having to spell out what you want. ROWID and LEVEL are examples of pseudo-columns. In this example, 2+3 is an Expression . Like pseudo-columns, expressions can usually appear in the ORDER BY clause, even though they are not actually stored in the table.)
    It's sorting first by the NUMBER 5 (= 2 + 3), not the 5th column. Since the NUMBER 5 has the same value on every row, every row ties for first place. The result of sorting by any constant is the same; the results are the same as not including that exrpression in the ORDER BY clause. That is the ORDER BY clause you posted is equivalent to
    ORDER BY  2 + 3
    ,         'foo'
    ,         SYSDATE
    ,         NULL
    ,         stu_id
    and it's also equivalent to
    ORDER BY  stu_id
    None of the expressions except stu_id vary from row to row, so none of them affect the sorting.
    (b) If there exist a 5th column in the table student and we specify it in the select statement's select list (5 column names) then why its not considering it as 5 and sorting the data according that?
    As Solomon said, only number literals are taken to mean a column. 5 is a number literal, so
    ORDER BY  5
    means "sort by the 5th column", but 2 + 3 is not a literal (it's an expression that happens to include a couple of literals), so
    ORDER BY  2 + 3
    does not refer to the 5th colunmn.
    (c) Is it true that, any expression we write is getting evaluated to NULL? Then is it that ORDER BY NULL evaluates to no ordering and the parser searches for next column-name or position (if specified) ?
    No, when you way "ORDER BY x", the rows with lower values of x come first, followed by rows with higher values of x. Rows with the same value of x will be together, in no particular order with respect to each other (unless there is a tie-breaker expression later in the ORDER BY clause.
    That's exactly what happens when you say "ORDER BY NULL". NULL has the same value (actually, the same lack of any value) on all rows, so all the rows have the same value of x, and they will appear in no particular order.
    (d) if neither then, what's the reason of the ignorance?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points