Using ROW_NUMBER and PARTITION to sequentially renumber a field in a new column?

The code I’ve compiled so far … (Specifically focus on the BUILD_NUM part)

SELECT        CNS_PID, CNS_BID, ROW_NUMBER() OVER(PARTITION BY CNS_PID ORDER BY CNS_PID) AS BUILD_NUM, CNS_SECT_NUM
FROM          REAL_PROP.CONSTRCOM 

Works when there are distinct values in CNS_BID, but it doesn’t work if CNS_BID occasionally repeats, which it can because a BID (buidling) can have multiple sections (CNS_SECT_NUM)

Here’s my (not what I need) results… (BUILD_NUM)

Here’s what I need… (BUILD_NUM)

So basically, For each CNS_PID I need each unique CNS_BID to be identified/renumbered 1,2,3,4, etc, which then RESETS for each CNS_PID.

I want to be able to know at quick glance that this CNS_PID has 4 distinct CNS_BID’s, (Since each CNS_BID has distinct CNS_SECT_NUMS) If there’s no way to do this, that’s fine, I will just be stuck with CNS_BID as the identifier, it’s just not as orderly and intuitive.

>Solution :

You want DENSE_RANK() instead of ROW_NUMBER():

SELECT     CNS_PID, CNS_BID, DENSE_RANK() OVER(PARTITION BY cns_pid ORDER BY cns_bid) AS BUILD_NUM, CNS_SECT_NUM
FROM       REAL_PROP.CONSTRCOM 
ORDER BY   CNS_PID, CNS_BID

See it work here:

https://dbfiddle.uk/CEtc_iUT