DENSE_RANK not working as expected

Posted on

Question :

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).enter image description here

Answer :

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).

Leave a Reply

Your email address will not be published.