All Forums Database
Srividhya80_b 12 posts Joined 07/05
14 Jun 2016
Is it an ordering - How do I generate a column that gives me the seq no of the select
Hi All

I have the following table 


CREATE MULTISET TABLE HASH_V ,FALLBACK ( 
   hashVal INTEGER 
) 
 PRIMARY INDEX ( hashVal );
--data inserted in this order

insert into HASH_V values (1);
insert into HASH_V values (5);
insert into HASH_V values (8);
insert into HASH_V values (3);
insert into HASH_V values (4);
insert into HASH_V values (7);
insert into HASH_V values (0);
insert into HASH_V values (10);
select 
hashVal
from HASH_V;
Get output in this sequence...

7
0
5
10
3
1
8
4

--I wanted a sequence value against it hence I tried

select 
csum(1,1)
hashVal
from HASH_V

--output 
1,3
2,8
3,4
4,0
5,10
6,1
7,5
8,7


Any Idea why the order is different? Does select give me the rows randomly?

Alternately I am looking for rowid (like oracle)- the sequence in which it is physically stored. rownumber will not work as I will have to use order by. 

 

Regards Srividhya

yuvaevergreen 93 posts Joined 07/09
14 Jun 2016

Rowid cannot be retrieved like Oracle afaik..

Srividhya80_b 12 posts Joined 07/05
14 Jun 2016

Thanks. I thought so. There was a rowid column that is no longer available. Can someone help me understand why? Is it to do with mpp architecture?

Regards Srividhya

CarlosAL 512 posts Joined 04/08
14 Jun 2016

Hi.
SQL SELECT does NOT guarantee (even in Oracle) any order of the rows returned if no ORDER BY clause is used.
The Oracle ROWID does not provide an order of insertion. It is related to where the row is stored (datablock, row inside the datablock, datafile). It is not immutable and can be reused if you delete a row and insert a new one.
Cheers.
Carlos.

You must sign in to leave a comment.