4

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
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Siva
  • 9,043
  • 12
  • 40
  • 63

2 Answers2

2

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

VBoka
  • 8,995
  • 3
  • 16
  • 24
  • You are welcome. Happy to be of any assistance. Do check the link where I have founded the answer from. It has some explanations and maybe you can vote up that answer to :) Cheers! – VBoka Oct 25 '19 at 05:08
1

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

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • Thanks for answer, but I am getting the resultset as a table output, can't hard code in query – Siva Oct 25 '19 at 05:03
  • you're welcome @Siva , butI cannot see any hardcoding. – Barbaros Özhan Oct 25 '19 at 05:06
  • I mean `select 8100,161 from dual` this part, I get `8100,161` as a result of query – Siva Oct 25 '19 at 05:16
  • 2
    @Siva - The WITH subquery just exists as a way to fake your table. It is a common technique on this site and other forums, to save the responders from needing to write `create table`, `insert` and `drop table` statements. It doesn't affect the validity of the solution. – APC Oct 25 '19 at 06:08