The problem with using the dynamic sql methods to number your rows is that the query will take a long time on large tables. Niether will you get unique row identities when you are grouping by fields that contain duplicate values. If you are using SQL Server 2K, A safer and more uniform way to accomplish this is to create a user defined function that inserts your select statement along with an identity value into a table variable. As Jonathan mentioned, I think you will also see a large performance benefit on large tables, and you can employ simpler SQL select statements to achieve your goal. The following is sample code to duplicate the dynamic sql example for the pubs authors table:

Create function dbo.udf_rankauthors

(

)

RETURNS @retAuthors TABLE

(IDX int Identity(1,1),

au_lname varchar(40),

au_fname varchar(20)

)

AS

---- -------------------------------------------------------------------*/

BEGIN

Insert Into @retAuthors

select au_lname, au_fname

from authors

order by au_lname, au_fname

RETURN

END

Regards,

Tom

Thomas Farren