Dynamically numbering distinct sql rows in select statement
row-number, sql, sql-server, t-sql
Solution
Use like this.
select ROW_NUMBER() OVER(order by name), * from
(Select distinct name from @table where %rules) as mytable
Problem
How would I dynamically number rows in a query similar to this: ``` Select distinct name from @table where %rules ``` When I add ROW_NUMBER() OVER() I lose my distinct property, and it returns every element in table, with a unique row number. ``` Select distinct ROW_NUMBER OVER(order by name), name from @table where %rules ``` I don't want to create a temporary table, otherwise I would make a primary key for the temporary table and have it insert row numbers that way. Thanks in advance!