CTE Issues for Selecting Distinct Values and assigned NEWID() to those values

I'm having an issue with some CTE code I'm working with. My current goal is that I have a table that has a 'Type' column. I want to select all the DISTINCT types from that 'Type' column and, for each type, assign a value of NEWID() to a separate column.

Heres a sample table that im starting with:

    WITH unique_gen_id AS (
        SELECT DISTINCT type, NEWID() AS unique_id
        FROM tmp
        )

    UPDATE t
    SET t.unique_id = u.unique_id
    FROM tmp t  INNER JOIN
    unique_gen_id u ON t.type = u.type

This query almost works-- it assigns a "NEWID()" unique value to each respective "Type" with a few mishaps. (Type is not specific to either an int or character, could be anything).

As you can see, the query returned almost fine. It failed, however, with "Type" of '4' as it assigned 2 separate 'NEWIDS()' when its supposed to match all the way through.

It gets worse with different data-- I tried on different data using different 'Types' (For example, I had 100 records with Type of "1" that returned 100 unique IDs for each record when its supposed to be 1 NEWID() for all of Type "1", then a new NEWID() for a different type etc etc) and it was catastrophic.

The problem is that your CTE isn't returning distinct IDs. DISTINCT applies to the entire SELECT list, not just the following column. Since NEWID() returns a different ID for each row, you get duplicate types because they have different IDs.

Instead of SELECT DISTINCT, use GROUP BY type to get one row per type. Use an aggregation function such as MAX() or MIN() to pick one of the IDs.

WITH unique_gen_id AS (
    SELECT type, MAX(NEWID()) AS unique_id
    FROM tmp
    GROUP BY type
)