All Forums Database
SR_Stage 1 post Joined 08/11
03 Jun 2013
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?  

dnoeth 4628 posts Joined 11/04
03 Jun 2013

DISTINCT is processed after ROW_NUMBER which creates unique values :-)
You need to move the DISTINCT into a Derived Table:

  ( SELECT DISTINCT ... FROM ...) AS dt 



You must sign in to leave a comment.