Dynamically numbering distinct sql rows in select statement

Question

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!

Answer 1

Use like this.

select ROW_NUMBER() OVER(order by name), * from 
(Select distinct name from @table where %rules) as mytable