prob column referencing by number!!

Go down

prob column referencing by number!!

Post by mightyganesh on Wed Mar 24, 2010 2:39 pm

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" !
http://unixcpp.forumotion.com/rdbms-oracle-f4/explanation-for-create-table-t125.htm
avatar
mightyganesh

Posts : 53
Points : 92
Join date : 2010-02-26
Age : 30

View user profile http://ganeshguna.blogspot.com

Back to top Go down

Re: prob column referencing by number!!

Post by Mahee on Wed Mar 24, 2010 3:46 pm

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...
avatar
Mahee

Posts : 29
Points : 233
Join date : 2010-03-03
Location : Haldia

View user profile

Back to top Go down

Re: prob column referencing by number!!

Post by Mahee on Wed Mar 24, 2010 3:56 pm

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
avatar
Mahee

Posts : 29
Points : 233
Join date : 2010-03-03
Location : Haldia

View user profile

Back to top Go down

Re: prob column referencing by number!!

Post by Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum