rank a column with out ordering in oracle

user_2293560 0
Viewed 702

I have the data as below, When I apply dense_rank by ordering id column, I am getting rank according to the order of integers but I need to rank as the records are displayed when run a query:

Data from query:

Rid   id

8100  161
8101   2
8102   2
8103   2
8104  156

When I apply dense_rank over order by id then I am getting

Rid   id    rank

8100  161    3
8101   2     1
8102   2     1
8103   2     1
8104  156    2

But my requirement is to get in below way:

Rid   id    rank

8100  161    1
8101   2     2
8102   2     2
8103   2     2
8104  156    3

Used row_number as well but the result is not as expected, not sure what option would be the better way.

Any help is appreciated.

Thanks

Edit------------------------------

Query used

Select rid, id,dense_rank() over (order by id) row_num
from table
rank analytic-functions oracle sql
2 Answers

I have adjusted solution from here: DENSE_RANK according to particular order for your need.

I am not sure if I should mark this as duplicate because on this link above there is no ORACLE tag. If more experience members think I should please do comment and I will do so and delete this answer.

Here is the adjusted code and demo:

SELECT t2.rid
       , t2.id
       , DENSE_RANK() OVER (ORDER BY t2.max_rid)
FROM (
  SELECT MAX(t1.rid) OVER (PARTITION BY t1.grupa) AS max_rid
         , t1.rid
         , t1.id
  FROM (       
    SELECT rid
           , id
           ,ROW_NUMBER() OVER (ORDER BY rid) - ROW_NUMBER() OVER (PARTITION BY id ORDER BY rid) AS grupa
    FROM test_table) t1 )  t2
ORDER BY rid

DEMO

user_65650380

You can use sum() aggregation containing (order by rid) after getting the values from lag() analytic function within the first query

with tab( rid,id ) as
(
    select 8100,161 from dual union all              
    select 8101,2   from dual union all              
    select 8102,2   from dual union all              
    select 8103,2   from dual union all              
    select 8104,156 from dual
), t2 as 
   (
   select t.*, lag(id,1,0) over (order by rid) lg
     from tab t
   )
   select rid, id, sum(case when lg!=id then 1 else 0 end) over (order by rid) as row_num
     from t2

Demo

user_58413060
Related