5 Replies Latest reply: Feb 10, 2013 2:40 PM by Frank Kulash RSS

    ORDER BY clause with expression

    990239
      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
          (a), but it is not appending, just calculating.

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

            SY.
            • 3. Re: ORDER BY clause with expression
              damorgan
              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
                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
                  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?