CODEWITHSUNDEEP
sqldb2
ashokkumar angadi
ashokkumar angadi

Reputation: 21

Not able to create view for query that involves row_number() function in db2

I am not able to create view for queries that involves row_number().

Just for example, I have sample table with one column Id. When I try to create view in the following way it gives an error.

create or replace view sample_view
(
    ID
)

AS
select Id from (
    select Id,row_number() over (partition  by id) as rownum
    from sample
    where rownum =1 )

Error message : No authorized routine named "ROW_NUMBER" of type "FUNCTION" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.16.53

Not sure why this error is being thrown while creating view, But when I try to run this query:

 select Id from (
    select Id,row_number() over (partition  by id) as rownum
    from sample
    where rownum =1 )

It's getting executed successfully.

Upvotes: 0

Views: 421

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

The where goes in the outer query, not the subquery:

select Id
from (select Id, row_number() over (partition by id order by <something goes here>) as rownum
      from sample
     ) s
where rownum = 1;

I assume you have more complex code, because this query is more easily written as:

select distinct Id
from sample;

EDIT:

DB2 also supports fetch first 1 row only, which might be simpler for this purpose:

select Id
from sample
order by <something usually goes here>
fetch first 1 row only;

Upvotes: 1

Related Questions