Is it an ordering - How do I generate a column that gives me the seq no of the select

Database
Enthusiast

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. 

3 REPLIES
Enthusiast

Re: Is it an ordering - How do I generate a column that gives me the seq no of the select

Rowid cannot be retrieved like Oracle afaik..

Enthusiast

Re: Is it an ordering - How do I generate a column that gives me the seq no of the select

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?

Junior Supporter

Re: Is it an ordering - How do I generate a column that gives me the seq no of the select

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.