Home » SQL & PL/SQL » SQL & PL/SQL » ORDER BY
ORDER BY [message #586848] Tue, 11 June 2013 05:39 Go to next message
mercyjhansi
Messages: 4
Registered: June 2013
Junior Member
The order by clause i used in insert subquery is not working. its not ordering as per the order mentioned. Why so?
insert into myt2 (WONO,
TASKDESC,
TASKREVNO,
SIGNOFF_MECHNAME,
SIGNOFF_INSPNAME,
MEC_LICENCENO,
MEC_REMARKS,
TASKSTATUS,
MEC_SIGNOFFSTATUS,
MAINTTYPE,
MEC_SIGOFFDATE,
INS_LICENCENO,
INS_SIGOFFDATE,
TASKNO,
INS_SIGNOFFSTATUS,
WORKCENTER,
SUBTASK,
INS_REMARKS)
select WONO,
TASKDESC,
TASKREVNO,
SIGNOFF_MECHNAME,
SIGNOFF_INSPNAME,
MEC_LICENCENO,
MEC_REMARKS,
TASKSTATUS,
MEC_SIGNOFFSTATUS,
MAINTTYPE,
MEC_SIGOFFDATE,
INS_LICENCENO,
INS_SIGOFFDATE,
TASKNO,
INS_SIGNOFFSTATUS,
WORKCENTER,
SUBTASK,
INS_REMARKS from myt1 order by WORKCENTER,WONO,TASKNO

Report message to a moderator

Re: ORDER BY [message #586849 is a reply to message #586848] Tue, 11 June 2013 05:43 Go to previous messageGo to next message
Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ORDER BY is useless in this context anyway, so - just remove it. If you want to retrieve values in a certain order, sort these records within the SELECT statement:
select ...
from ...
where ...
order by workcenter, wono, taskno      --> here

Report message to a moderator

Re: ORDER BY [message #586850 is a reply to message #586849] Tue, 11 June 2013 05:48 Go to previous messageGo to next message
mercyjhansi
Messages: 4
Registered: June 2013
Junior Member
Yes select will work. My requirement is i need to insert in that particular order into another table. Is that possible? why so useless? I see the order by clause in insert subquery syntax in oracle document. Why so its not working but?

Report message to a moderator

Re: ORDER BY [message #586851 is a reply to message #586850] Tue, 11 June 2013 05:51 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Because unless it's an IOT, it'll put them where it can fit them. (Unless you're forcing it not to)

Report message to a moderator

Re: ORDER BY [message #586853 is a reply to message #586851] Tue, 11 June 2013 05:55 Go to previous messageGo to next message
mercyjhansi
Messages: 4
Registered: June 2013
Junior Member
oh i see, pardon me, i have no idea about IOT. Whats an IOT?

Report message to a moderator

Re: ORDER BY [message #586854 is a reply to message #586853] Tue, 11 June 2013 05:56 Go to previous messageGo to next message
Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Index organized table.

Report message to a moderator

Re: ORDER BY [message #586856 is a reply to message #586854] Tue, 11 June 2013 06:12 Go to previous messageGo to next message
mercyjhansi
Messages: 4
Registered: June 2013
Junior Member
Oh I see. Fine. So you mean if there is index for that table, the order by will work? I tried that. Created an index like this:
create index hwo_idx2 on myt2(WORKCENTER,WONO,TASKNO)
for both tables and tried then, but still it doesn't order correctly.

Report message to a moderator

Re: ORDER BY [message #586857 is a reply to message #586856] Tue, 11 June 2013 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
So you mean if there is index for that table, the order by will work?


No, IOT is a special type of table.
Please refer to the documentation.

Regards
Michel

Report message to a moderator

Re: ORDER BY [message #586858 is a reply to message #586856] Tue, 11 June 2013 06:16 Go to previous messageGo to next message
cookiemonster
Messages: 13972
Registered: September 2008
Location: Rainy Manchester
Senior Member
No. Index organized table is a special type of table, not just a table with an index on it. Look it up in the documentation.
I doubt you need it though.
You need to understand that rows in a normal table are not stored in any particular order, and they don't need to be.
IOT is a special case that's used for certain performance issues.

So unless there is a particular performance issue you are trying to solve, stop worrying about the order records are inserted in.

Report message to a moderator

Re: ORDER BY [message #586859 is a reply to message #586856] Tue, 11 June 2013 06:16 Go to previous messageGo to next message
Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Wrong. You have just created an index, but that doesn't make that table an IOT. You create it that way (Managing Index-Organized tables), for example
CREATE TABLE test
  (col1 number primary key,
   col2 number
  ) 
organization index;

Report message to a moderator

Re: ORDER BY [message #586860 is a reply to message #586856] Tue, 11 June 2013 06:17 Go to previous messageGo to next message
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
Senior Member
Why does the data need to be stored in a certain order when you can SELECT... ORDER BY? Oracle doesn't guarantee the "order" of data in a table.

Report message to a moderator

Re: ORDER BY [message #587118 is a reply to message #586860] Wed, 12 June 2013 10:40 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member
For this can we use the concept of Oracle Rowid

Report message to a moderator

Re: ORDER BY [message #587120 is a reply to message #587118] Wed, 12 June 2013 10:48 Go to previous messageGo to next message
cookiemonster
Messages: 13972
Registered: September 2008
Location: Rainy Manchester
Senior Member
No. The order of rowid is meaningless.

Report message to a moderator

Re: ORDER BY [message #587121 is a reply to message #587118] Wed, 12 June 2013 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What is the relation between ROWID and "order by WORKCENTER,WONO,TASKNO"?

Regards
Michel

[Edit: Gee! cookiemonster answered the question before I posted it]

[Updated on: Wed, 12 June 2013 10:49]

Report message to a moderator

Re: ORDER BY [message #587125 is a reply to message #587121] Wed, 12 June 2013 10:51 Go to previous message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

OK Thanks

Report message to a moderator

Previous Topic: Query to get whether a string contains 2 upper characters and numbers using regular_exp
Next Topic: sql query with where condition
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Sep 13 09:07:33 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.