How to add Row_number to table function results

Question

I have a function that returns groups(clusters) from a table..

create function dbo.ftAllClusters() returns table as return
select distinct Cluster from Company

Now I need to add RowNumber for each of the row returned..

Answer 1

There is build in Function in SQL called ROW_NUMBER()

EDIT: According to comment by losing distinct function in order by You can try to SubSelet

    create function dbo.ftAllClusters() returns table as return
    Select 
       Cluster
      ,(ROW_NUMBER() OVER  (order by Cluster)) as RN
   from (
         Select 
          distinct 
          ,Cluster
          from Company ) as Comp

Or You can try using Group BY instead of Distinc ( I even think that that is little bit faster )

Select 
   Cluster
  ,(ROW_NUMBER() OVER  (order by Cluster)) as RN
from Company 
group by Cluster

Answer 2

Here is the solution I worked out, Is there a better way ?

ALTER function dbo.ftAllClusters() returns table as return
With CTE(comno) as
(select distinct Cluster from  company)

select id=Row_number() over(order by comno),comno from cte

Answer 3

SELECT Cluster, ROW_NUMBER() OVER(ORDER BY Cluster) AS RowNo
FROM
(
    SELECT DISTINCT Cluster
    FROM Company
) x

Or...

SELECT Cluster, ROW_NUMBER() OVER (ORDER BY Cluster) AS RowNo
FROM Company
GROUP BY Cluster