Satish_Chaudhary_2113

 Profile Answers by Satish_Chaudhary_2113 Questions by Satish_Chaudhary_2113

• Nov 18th, 2007

 

The order by clause not necessarily maintains the column number, we can specify an expression,or an alias,or column position as the sort conditions.

Waseem Mehmood

 Profile Answers by Waseem Mehmood 

• Dec 18th, 2007

 

Every Column have unique number in the table, when we write ORDER BY Clause with number then it referes to that unique number and display the result.

You can see the column Id as per below query

SELECT COLUMN_NAME, COLUMN_ID

FROM USER_TAB_COLUMNS

WHERE TABLE_NAME = 'EMP'

Regards
Waseem

krishnaindia2007

 Profile Answers by krishnaindia2007 

• May 16th, 2008

 

There is no relation between column_id in user_tab_columns and column number in order by clause.
For example
Select ename,job, sal from emp
order by 1;

Here it will arrange data in the order of employee name where as column ID  of ENAME in emp is 2.  Here the number specified in order by clause represents its position in select statement.

Order by clause with column number is generally used with set operators.

rahul.katke

 Profile Answers by rahul.katke 

• Jun 24th, 2008

 

The column numbers are used so that you can do the order by without keying in all the column names in the order by clause.

For eg.

select empno, ename from emp order by empno; -- qry 1

could be replaced by

select empno, ename from emp order by 1; -- qry 2

If you wanted to change the order as may be order by ename asc, that would have required doing...

select empno, ename from emp order by ename; -- qry 3

instead of just changing the qry to ...

select empno, ename from emp order by 2; -- qry 4

Just worry about the time spent to select from a table based on different orders of ascending and descending of different columns which have long names!!! If it is a one time activity, we would go for column names, but for our quick results from our query, I guess it would be much quicker (to type) if we just enlist the column numbers (which we would only for our own frequent use, such as in unit testing).

Also, assuming the table (say log_table) has a column such as when (i.e., the timestamp), and you wanted only records for today, but in desc order, so that you know the progress of say some procedure, which does some processing and in turn logs it in our table, say log_table. In such a case, the column position of timestamp column always remains the same, unless the table is "altered" or "dropped & re-created". Assuming the log_table has a structure such as:

log_process    varchar2(255),
when              date,
log_message  varchar2(255)

Here, the query to get today's records, but all in descending order would be:

select * from log_table
where when >= trunc(sysdate)
order by 2 desc;  -- qry 5

Please note that the 2 above is nothing but the column_id value of user_tab_cols for the when column of log_table.

It could be confirmed by :

select column_id from user_tab_cols where table_name = 'LOG_TABLE' and column_name = 'WHEN';

There is a serious disadvantage with using column numbers in the order by clause.

Suppose the column when was dropped, and may be just re-added to have the following new structure.

log_process    varchar2(255),
log_message  varchar2(255),
when              date

And now, if your qry still remains the above: i.e.,

select * from log_table
where when >= trunc(sysdate)
order by 2 desc;  -- qry 5

Then, though you would get only today's records from the log_table, they would be ordered only by log_process, thus, giving you unexpected output.

So, use it only if no one else would modify the selected columns, selected expressions, or the table column positions.

Ideally, while small unit tests, the column numbers are used, and when the code is actually integrated with other pieces, the column names are the best to use.
 
Hope it helps!

- Rahul Katke.