How do I get the row number?

I have tryed to find a way to include the row number in a querry but without result.

I have a table select some rows ordered and now I will add a column called rank

How do I get the rownumber in the field rank ?

Thanks in advance

Jan OThe way I'd suggest assigning a row number to your resultset is to INSERT your resultset into a temporary table or table variable which has an IDENTITY column, something like this:

-- set up a table variable to hold the resultset
DECLARE @.myTable (Rank int identity(1,1) primary_key,
 ColumnA varchar(20),
 ColumnB varchar(20)
 )
-- insert the resultset into the table variable
INSERT INTO
 @.myTable
(
 ColumnA,
 ColumnB
)
SELECT
 ColumnA,
 ColumnB
FROM
 someTable
ORDER BY
 someCriteria
-- return the resultset to the calling program in rank order, including the rank column
SELECT
 Rank,
 ColumnA,
 ColumnB
FROM
 @.myTable
ORDER BY
 Rank

Terri|||Thanks,

Have hoped not to do it so, a lot of other calculation dependences.

Jan|||Well, an alternative (which performs poorly) would be at the very bottom of this link:Returning a Row Number in a Query It involves using a subquery for every row in your resultset and could be horrendous performance-wise.

Can you just assign the rank in the front end?

Terri|||I agree with Terri. If possible, assign in the front end. I have used the temp table solution before, as well. It really depends on why you need to have the rownumber. If it is just for display purposes, it should be no problem to create on the front end when binding your resultset.|||Thanks again.

I have started to solve the "problem" in the front end.
I have a datagrid with paging so I have a few lines left.
Got it working whitout paging , but I had prefered asystem rownr from SQL.

But you cant get everything for free :)

Thanks
Jan