| Basic sorting concept [message #644868] |
Thu, 19 November 2015 04:04  |
 |
nciteamo
Messages: 27
Registered: October 2014
|
Junior Member |
|
|
Hello Guys
I have script like this below
CREATE TABLE TEMP (NO NUMBER);
DECLARE
V_COUNTER NUMBER := 1;
BEGIN
DELETE TEMP;
FOR X IN 1..1000 LOOP
INSERT INTO TEMP VALUES (V_COUNTER);
V_COUNTER := V_COUNTER + 1;
END LOOP;
COMMIT;
END;
i have a question, maybe a basic question for you,
when 1 put v_counter to 1000, the result will be incrementaly sorted properly start with 1 until 1000 in the table,
but when i put v_counter to 5000 then the result will be incrementaly sorted randomly.
is the difference (sorted/ not sorted) caused by the numbers of v_counter?
why does it happen?
Thanks Before
|
|
|
|
|
|
|
|
| Re: Basic sorting concept [message #644871 is a reply to message #644869] |
Thu, 19 November 2015 04:13  |
|
nciteamo
Messages: 27
Registered: October 2014
|
Junior Member |
|
|
why when i put v_counter 1000 then i type select * from temp, without order by and the result will be sorted automaicly start with 1, but when i put v_counter 5000 then i type select * from temp without order the result aint sorted?
[Updated on: Thu, 19 November 2015 04:14] Report message to a moderator |
|
|
|
|
|
| Re: Basic sorting concept [message #644877 is a reply to message #644871] |
Thu, 19 November 2015 04:46 |
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Luck.
Nothing more, nothing less.
Someone else could do exactly the same as you on some other oracle DB and get the opposite result.
Worrying about when the data comes back in the order of insertion without an ORDER BY is an utter waste of time, just assume that if you don't have an order by the order will be random. Even if it isn't random right now there is absolutely nothing stopping it from becoming random tomorrow.
|
|
|
|
|
|
|
|
|
|
| Re: Basic sorting concept [message #644905 is a reply to message #644903] |
Thu, 19 November 2015 11:20 |
 |
EdStevens
Messages: 1377
Registered: September 2013
|
Senior Member |
|
|
CraigB wrote on Thu, 19 November 2015 09:07The order in which values are inserted into a table IS NOT the order in which they will be returned from the table (read the doc's it is clearly stated). As LittleFoot stated, if you want your results returned in a specific order, then use the ORDER BY clause.
And believe it or not, even that needs to be clarified as "use the ORDER BY clause on your SELECT statements."
We have seen people try something like this
insert into mytable values (select mycol from other_table order by mycol);
Of course that comes from the same mis-guided notion that rows have some order in the table.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
