DENSE_RANK () without duplication

Question

Here's what my data looks like:

| col1 | col2 | denserank | whatiwant | |------|------|-----------|-----------| | 1 | 1 | 1 | 1 | | 2 | 1 | 1 | 1 | | 3 | 2 | 2 | 2 | | 4 | 2 | 2 | 2 | | 5 | 1 | 1 | 3 | | 6 | 2 | 2 | 4 | | 7 | 2 | 2 | 4 | | 8 | 3 | 3 | 5 | 

Here is the query that I still have:

 SELECT col1, col2, DENSE_RANK() OVER (ORDER BY COL2) AS [denserank] FROM [table1] ORDER BY [col1] asc 

What I would like to achieve is that my denserank column be increment every time the col2 value changes (even if this value is reused). I cannot actually order the column on which I have denserank, so this will not work). See whatiwant column for an example.

Is there a way to achieve this with DENSE_RANK() ? Or is there an alternative?

Answer 1

Try this with window functions:

 with t(col1 ,col2) as ( select 1 , 1 union all select 2 , 1 union all select 3 , 2 union all select 4 , 2 union all select 5 , 1 union all select 6 , 2 union all select 7 , 2 union all select 8 , 3 ) select t.col1, t.col2, sum(x) over ( order by col1 ) whatyouwant from ( select t.*, case when col2 = lag(col2) over ( order by col1 ) then 0 else 1 end x from t ) t order by col1; 

It produces:

It reads one table and forms a group of consecutive equal values ​​of col2 in ascending order of col1, and then finds a dense rank on it.

• x : set it to 0 if the previous line col2 matches this line col2 (in ascending order col1 ), otherwise 1
• whatyouwant : create groups with equal col2 values ​​in ascending order of col1 by doing the extra sum of the x value generated in the last step and your result.

Answer 2

I would do it with recursive cte as follows:

 declare @Dept table (col1 integer, col2 integer) insert into @Dept values(1, 1),(2, 1),(3, 2),(4, 2),(5, 1),(6, 2),(7, 2),(8, 3) ;with a as ( select col1, col2, ROW_NUMBER() over (order by col1) as rn from @Dept), s as (select col1, col2, rn, 1 as dr from a where rn=1 union all select a.col1, a.col2, a.rn, case when a.col2=s.col2 then s.dr else s.dr+1 end as dr from a inner join s on a.rn=s.rn+1) col1, col2, dr from s result: col1 col2 dr ----------- ----------- ----------- 1 1 1 2 1 1 3 2 2 4 2 2 5 1 3 6 2 4 7 2 4 8 3 5 

ROW_NUMBER is only required if your col1 values ​​are not sequential. If they are, you can use recursive cte right away

Answer 3

Here is one way to use the window aggregation function SUM OVER(Order by)

 SELECT col1,Col2, Sum(CASE WHEN a.prev_val = a.col2 THEN 0 ELSE 1 END) OVER(ORDER BY col1) AS whatiwant FROM (SELECT col1, col2, Lag(col2, 1)OVER(ORDER BY col1) AS prev_val FROM Yourtable) a ORDER BY col1; 

How it works:

LAG The window function is used to search for the previous col2 for each row ordered by col1

SUM OVER(Order by) will increase the number only if the previous col2 not equal to the current value of col2

Answer 4

I think this is possible in pure SQL using some spaces and tricks on the islands, but the path of least resistance may be to use a session variable in combination with LAG() to keep track of when your calculated dense rank changes value. In the query below, I use @a to track changes in dense ranking, and when this variable changes, it increases by 1.

 DECLARE @a int SET @a = 1 SELECT t.col1, t.col2, t.denserank, @a = CASE WHEN LAG(t.denserank, 1, 1) OVER (ORDER BY t.col1) = t.denserank THEN @a ELSE @a+1 END AS [whatiwant] FROM ( SELECT col1, col2, DENSE_RANK() OVER (ORDER BY COL2) AS [denserank] FROM [table1] ) t ORDER BY t.col1