CODEWITHSUNDEEP
oracle-databaseoracle11g
XING
XING

Reputation: 9886

Need rowid from a query

I am trying to get the rowid from a query. My query is :

Table: test ( PersonId number, AssetId number);

Query:

with abc as(
select      
       personid , 
       row_number() over(partition by personid order by personid,carid) rnk       
from test
--group by personid,carid,rowid
)
select rowid, abc.* from abc ;

and its throwing error.

ORA-01446: cannot select ROWID from,or sample,a view with DISTINCT,GROUP BY etc

Is there any way i can get the rowid (this way) or its not allowed to get rowid using the way am trying to get in Oracle. Can anyone throw some views here. Thanks.

Upvotes: 0

Views: 837

Answers (2)

vercelli
vercelli

Reputation: 4757

Try including rowid inside the with subquery ( I used an alias)

with abc as(
select      
       personid , 
       rowid as r, 
       row_number() over(partition by personid order by personid,carid) rnk       
from test
/***This was unwanted in question***/
--group by personid,carid,rowid
)
select r, personid, rnk 
 from abc ;

Upvotes: 2

MT0
MT0

Reputation: 167774

Using GROUP BY with ROWID will not aggregate rows as ROWID is unique so the size of each group will always be 1.

You can just do:

SELECT personid,
       ROW_NUMBER() OVER ( PARTITION BY personid ORDER BY carid ) AS rnk,
       ROWID
FROM   test;

Upvotes: 0

Related Questions