Row Number and Select Distinct Query

Database

Row Number and Select Distinct Query

Good morning TD community,

I've been working on a writing a distinct query that returns as a single concatenated string (it has to be fed into a system that is expecting fixed-length field based on position) and I have a requirement to number the rows.  I've attempted several methods of row numbers but I continue to get the result of all the rows being numbered before the distinct condition of the select is applied.  So I am expecting 13 rows, but I get 91 because if you don't use the distinct condition, that's how many rows there are.  I need number off the rows 1-13. 

Is there a way to apply a row number to the distinctly selected rows?  

1 REPLY
Junior Contributor

Re: Row Number and Select Distinct Query

DISTINCT is processed after ROW_NUMBER which creates unique values :-)

You need to move the DISTINCT into a Derived Table:

SELECT ROW_NUMBER(....), dt.*
FROM
( SELECT DISTINCT ... FROM ...) AS dt

Dieter