CODEWITHSUNDEEP
sqloracle-databaserank
JavaSat
JavaSat

Reputation: 34

Use of rank function in nested way in oracle

I am facing some issues with rank function when I am trying to use rank function on a select result set which is also using rank function.

Example sql query:

select a,b,c,
rank() over (partition by d order by d desc) as r_outer
from(
    select a,b,c,d from( 
                          select a,b,c,d,
                          rank() over (partition by e order by e desc) as r_inner  
                          from sample
                       )where r_inner=1
    );

Problem:

When I am using the above query, I am getting r_outer result as '1' every time although values of d are different.

Sample Data:

A B C D E
A1 B1 C1 1 1
A1 B1 C1 2 1

Expected results:

A B C R_OUTER
A1 B1 C1 1
A1 B1 C1 2

Current query results:

A B C R_OUTER
A1 B1 C1 1
A1 B1 C1 1

Upvotes: 0

Views: 210

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270051

I have no idea what you are trying to accomplish, but this logic:

rank() over (partition by d order by d desc)

is always going to return 1. And only 1. Why? The order by key and partition by key are the same. So, there is only one value in each partition. And the rank of a single value is always going to be 1.

Perhaps you intend:

rank() over (order by d desc)

However, this is really just a guess because your question has not provided enough information to know what you intend.

Upvotes: 1

Related Questions