SQL create dense rank based on mulitple columns

Question

I have a data set with two fields in which I need to create a dense rank for. When Field_1 is not null then Field_2 is null and vice-versa.

Field_1 Field_2
a   
        1
        2
        3
        4
b   
        7
        8
        9
c   
        15
        20
        25

Im trying to get to this

Field_1 Field_2 Field_3
a               1
        1       1
        2       1
        3       1
        4       1
b               2
        7       2
        8       2
        9       2
c               3
        15      3
        20      3
        25      3

I can order the data wit the following line:

row_number() over (partition by null order by Field_2,Field_1) as Field_3

but this does not give me the dense rank I am looking for.

Without any extra columns, how are we supposed to know that `1`-`4` are supposed to belong to the same group as `a`? — Mr. Llama, Oct 18 '18 at 14:11
Sigh..it's a rough data set. The data provided is an example but the `row_number` I provided is how the data is ordered. It is sequential. I was hoping to formulate a query to fill in the blanks in Field_1 but I do not want to do a recursive query. — AlmostThere, Oct 18 '18 at 14:15

score 0 · Accepted Answer · answered Oct 18 '18 at 14:26

0

Assuming you do have some sort of incremental row number column (we'll call it row_number), you can use a windowing clause to count the number of populated field_1 values:

COUNT(field_1) OVER (ORDER BY row_number ROWS UNBOUNDED PRECEDING) AS group_number

This makes it where each row counts the number of populated field_1 values that come before it (including itself) when ordered by row_number.

Here's some more details on the technique: https://stackoverflow.com/a/30862368/477563

answered Oct 18 '18 at 14:26

Mr. Llama

20,202
2
62
115

thanks that worked! I used 'Field_3' from my post as the `row_number` – AlmostThere Oct 18 '18 at 14:31
I also came up with a different way a well but is more complicated. `select qry1.Field_1 ,qry1.Field_2 ,Cast(Sum(qry1.Field_1_Binary) over (Partition By null Order By qry1.Field_3 Rows Between Unbounded Preceding and Current Row) as Float) as Rank_Field from ( Select Field_1 ,Field_2 ,row_number() over (partition by null order by Field_2,Field_1) as Field_3 ,case when Field_1 = null then 1 else 0 end as Field_1_Binary ) as qry1` – AlmostThere Oct 18 '18 at 14:32

SQL create dense rank based on mulitple columns

1 Answers1