prob column referencing by number!!

SQL> select * from empdept_336474;

DEPT_ID SPL_ID
---------- ----------
100 100
200 20
300 300
400 56

SQL> select * from empdept_336474 order by 2;

DEPT_ID SPL_ID
---------- ----------
200 20
400 56
100 100
300 300
this sorts the table using the second column as key!

SQL> select 1 from empdept_336474;

1
----------
1
1
1
1
this displays 4 1's (each 1 represent one row of output)

but
SQL> select 1 from empdept_336474 order by 2;
select 1 from empdept_336474 order by 2
*
ERROR at line 1:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression

when these 2 query are combined it produces an error ! can any one explain me y??

i think this may also have some relation with the Q asked " where 1= 2" !
https://unixcpp.forumotion.com/rdbms-oracle-f4/explanation-for-create-table-t125.htm

Try this,

sql> select dept_id from empdept_336474 order by 2;

u will get the same error...

select takes number as just number, whereas order by takes number as the column number...

You are getting that error because,

whenever you are using " order by <number> " the column corresponding to the number must also be there in the selection list...

For eg,
I said, for
sql> select dept_id from empdept_336474 order by 2;
you will be getting error..
because dept_id 's column number is 1, where as i give "order by 2"

if i execute
sql> select spl_id from empdept_336474 order by 2;
it will work because the column number of spl_id is 2...

If you are confused with this, try wit a table having more that 3 column having atleast 3 column in select and check for different order by number