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