CODEWITHSUNDEEP
sql-serverdense-rank
Sudi
Sudi

Reputation: 19

Need to skip Dense Rank in SQL Server

I want to generate Dense_Rank on a particular column but I want to provide default Rank on few value but the sequence should not break any suggestion.

enter image description here

Upvotes: 0

Views: 110

Answers (1)

Cetin Basoz
Cetin Basoz

Reputation: 23797

What you want is:

select *, 
  dense_rank() over (order by case when id <> 3 then id end)-1 as Ranking
from test
order by id;

DBFiddle demo

Probably this was what you meant in your comment:

IF OBJECT_ID('tempdb..#test') IS NOT NULL 
    DROP TABLE #test 
   create table #test (id int); 
  insert into #test (id) values (1),(2),(3),(4),(4),(5),(6),(7); 
  with mx(id) as
    (select max(id) from #test),
    data(id, idrev) as (select t.id, mx.id - t.id + 1 from #test t, mx)
    select id, 
      dense_rank() over (order by case when id <> 3 then idrev end)-1 as Ranking 
  from data order by id;

DBFiddle demo

Upvotes: 1

Related Questions