How do I use DISTINCT whilst using Next Value For Expression in SQL

Question

I have a query which uses the Next Value For to generate a unique ID and the rest of the columns are your usual Reference, Name and Type etc. The issue I have is that the Next Value For is generating duplicate rows - when I try and use SELECT DISTINCT I get the below error;

NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.

When I comment out the first Next Value For column, the duplicates are removed and I get the correct results i.e. unique rows, but when the Next Value For is not commented out, the duplicates are returned. I know I can use a Group By for every column, but there nearly 50 columns and was hoping there's a cleaner way of retaining the ID column (which uses the Next Value For) but only return unique rows. Below is a sample;

SELECT DISTINCT
CONVERT(VARCHAR(100), 333000000000000 + NEXT VALUE FOR[UnitE].CAPS_ID OVER(ORDER BY modudet.code, CONCAT(CONCAT(concat(modudet.code, 'CM'), '-'),  '2024'))) AS 'ID'
, CONVERT(VARCHAR(255), progdetail.programmecode) AS 'PROGRAMME' 
, CONVERT(VARCHAR(50), CONCAT(CONCAT(concat(modudet.code, 'CM'), '-'),  '2024')) AS 'REFERENCE' 
, CONVERT(VARCHAR(255), modudet.longtitle) AS 'NAME' 
, CONVERT(VARCHAR(10),'Module') AS 'TYPE'
 FROM [TABLE1] progdetail 
 JOIN [TABLE2] modudet ON progdetail .child_entitycuid = modudet.module_cuid
WHERE progdetail.programmecode in (SELECT DISTINCT programmecode FROM TABLE2 GROUP BY programmecode HAVING COUNT(*)=1) 

Any pointers would be greatly appreciated. Many thanks in advance.

UPDATE: following a suggestion by Dale K, I have attempted putting the DISTINCT in a subquery but it still yields the same duplicates.

Answer 1

You can create a view that does your CONVERT(VARCHAR(100), 333000000000000 + NEXT VALUE FOR[UnitE].CAPS_ID OVER(ORDER BY modudet.code, CONCAT(CONCAT(concat(modudet.code, 'CM'), '-'), '2024'))) AS 'ID' part. This view would select all columns that you need for this query and group by all the fields that you need to group by.

You will still have group by with multiple columns, but it will be in a reusable view and your queries from it will be cleaner.

Alternatively, you could have a computed hash from the columns that you want to group by (see https://dba.stackexchange.com/questions/220837/how-to-create-a-hash-computed-column-for-many-columns) and group by this hash.