row count in result set

Does anybody know how I can get a row count in my result set.
So first column should be 1, 2, 3 ...
Thanks,
BoontjeIf there's a pk in the resultset, you could use that to count with (not null etc). Otherwise I'd suggest a temp table with an identity column.|||what is producing this result set?

if it's a simple query against a single table, you could use a ranking self-join, then you won't need a temp table or identity

however, the ranking must be done based on the values in some column (ascending or descending)

FYI, Kaiowas, check this out -- IDENTITY() Function Isn't Reliable for Imposing Order on a Result Set (http://www.winnetmag.com/SQLServer/Article/ArticleID/43553/43553.html)|||The argument...

Is that the optimizer may apply the identity value BEFORE The ORDER BY

The example given:

SELECT EmpId, EmpSalary,
LastName, SalaryRank
=IDENTITY(int, 1, 1)
INTO ListOfHighestPaid-
Employees
FROM Employees
ORDER BY EmpSalary desc

Can be readdressed as

USE Northwind
GO

SET NOCOUNT ON
GO

SELECT *, RowNumber=IDENTITY(int, 1, 1)
INTO myTable99
FROM (
SELECT TOP 100 PERCENT EmployeeID
LastName, FirstName
FROM Employees
ORDER BY EmployeeID) AS XXX

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

I love the way they show how not to do it, but don't give a solution...except to wait for SQL 2005...when's the release?|||brett, your "readdressed" query isn't guaranteed to work correctly either

the part about a distributed query is what finally made me see the light

the solution?? find another way and don't rely on identity!|||brett, your "readdressed" query isn't guaranteed to work correctly either

How so?

the part about a distributed query is what finally made me see the light

Where did that come from?

the solution?? find another way and don't rely on identity!

Well sure...an arbitrary number is just that...what meaning will it have?|||How so? because your subquery has an ORDER BY in it

the part about a distributed query comes from here --Imagine that the Employees table has 100,000 rows and SQL Server breaks the query into four steps. Each step might be gathering its own set of rows and assigning identity values as SQL Server processes the rows. However, SQL Server wouldn't apply the ORDER BY clause until all four threads are finished gathering rows and SQL Server serializes each of the parallel streams back into a single step. In that context, using IDENTITY() might not give you the results you want.

yes, an identity is just an arbitrary number -- good of you to notice, because this is actually the crux of the MISuse of identity to impose sequence!

what meaning will it have? exactly!!!!!

in particular it won't necessarily reflect the correct order, will it

;)|||ummm...my derived table will be materialized BEFORE the ORDER BY, so I don't believe there is a problem...

And yes...this is like beating a dead horse

I should add this to the blog

Surrogate Keys - The Devils Spawn (http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx)