Select N Row in Oracle

Question

Suppose we are having the following data:

Key Value Desired Rank
--- ----- ------------
P1    0.6            2    
P1    0.6            2    
P1    0.6            2    
P2    0.8            1
P2    0.8            1
P3    0.6            3
P3    0.6            3

I want to select Distinct Keys ordered by Value DESC to be displayed in a grid that supports pagination.

I don’t know how to generate rank as the values displayed in Desired Rank column. So that I can paginate correctly over the data set

When I tried to use: DENSE_RANK() OVER(ORDER BY value), the result was

Key Value   DENSE_RANK() OVER(ORDER BY value)
--- ----- ------------
P1    0.6            2    
P1    0.6            2
P1    0.6            2    
P2    0.8            1
P2    0.8            1
P3    0.6            2    
P3    0.6            2

When I try to select the first two keys “rank between 1 and 2” I receive back 3 keys. And this ruins the required pagination mechanism.

Any ideas?

Thanks

Answer 1

If you want the distinct keys and values, why not use distinct?

select distinct
  t.Key,
  t.Value
from
  YourTable t
order by
  t.value

Do you actualle need the rank?

If you do, you still could

select distinct
  t.Key,
  t.Value,
  dense_rank() over () order by (t.Value, t.Key) as Rank
from
  YourTable t
order by
  t.value

This whould work without the distinct as well.