Select N Row in Oracle

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

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.

'When I try to select the first two keys “rank between 1 and 2” I receive back 3 keys.'

That is because you are ordering just by VALUE, so all KEYS with the same value are assigned the same rank. So you need to include the KEY in the ordering clause. Like this:

DENSE_RANK() OVER (ORDER BY key ASC, value DESC)