DENSE_RANK not working as expected

All we need is an easy explanation of the problem, so here it is.

I have a situation where I need to assign a unique "ID" for every unique address. Each row contains other columns as well, besides the "address" columns.

My query is:

SELECT umLocationID,
       arcs_name1,
       arcs_name2,
       arcs_addr1,
       arcs_addr2,
       arcs_city,
       arcs_state,
       arcs_zip,
       arcs_country,
       arcs_cust_class,
       RANK() OVER(PARTITION BY arcs_name1,
                                arcs_name2,
                                arcs_addr1,
                                arcs_addr2,
                                arcs_city,
                                arcs_state,
                                arcs_zip,
                                arcs_country 
                   ORDER BY arcs_name1,
                            arcs_name2,
                            arcs_addr1,
                            arcs_addr2,
                            arcs_city,
                            arcs_state,
                            arcs_zip,
                            arcs_country) AS 'Rank_Address#',
       DENSE_RANK() OVER(PARTITION BY arcs_name1,
                                      arcs_name2,
                                      arcs_addr1,
                                      arcs_addr2,
                                      arcs_city,
                                      arcs_state,
                                      arcs_zip,
                                      arcs_country 
                         ORDER BY arcs_name1,
                                  arcs_name2,
                                  arcs_addr1,
                                  arcs_addr2,
                                  arcs_city,
                                  arcs_state,
                                  arcs_zip,
                                  arcs_country) AS 'Dense_Rank_Address#'
  FROM #Cleanout_artstcid2

Both my RANK() and DENSE_RANK() are giving the same result, a value of 1 for every row of the query results. What am I doing wrong? I know there is a need to use DISTINCT with DENSE_RANK but I cannot use it given the requirements for my overall query. Here is a screenshot of what I am getting (partial columns).DENSE_RANK not working as expected

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Remove your PARTITION clause. The window functions are calculated within the PARTITION itself, and it sounds like you’re trying to rank the data globally (i.e. uniquely rank the addresses globally).

Or adjust the PARTITION clause to reflect the window of what you want the rank to be calculated across. For example, if you wanted to rank the data within the field arcs_name1 then you’d use PARTITION arcs_name1 only (but leave your ORDER BY clause as is) and the ranking would only be within the grouping of each value of arcs_name1 (resetting it’s counter between each unique value of arcs_name1).

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply