SQL to return a record counter per row?

I'd like to have a query that returns the number of the record as a column in the record set, e.g. something like
SELECT <count of current Row> , * from <My Table> where <whatever>

so that the results are

1    <column>  <column>   etc
2    <column>  <column>   etc
3    <column>  <column>   etc
4    <column>  <column>   etc

Note that the counter I need is for the returned set, not the identity col or equivalent RowID of the underlying table.
As I recall there was some trick to do this by creating a table of integers, but I forget what it is.
LVL 16
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Hi JohnBPrice,

SELECT Rank = (SELECT Count(*) From urTable b WHERE a.PK >= b.PK),
Column1 ,Column2
FROM urTable a

Aneesh RetnakaranDatabase AdministratorCommented:
SELECT Rank = (SELECT Count(*) From urTable b WHERE a.PK > b.PK)+1,
Column1 ,Column2
FROM urTable a

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aneesh RetnakaranDatabase AdministratorCommented:
the second one is the corrected one ..
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
There's no easy way to do that in SQL 2K.  You can use:
(1) an intermediate temp table with an IDENTITY column <or>
(2) do an ordered list with a subquery to determine the relative row number (note: this can be an expensive subquery).

SELECT IDENTITY(INT, 1, 1) AS [Row#], ...
INTO #report
FROM ...

FROM #report

SELECT (SELECT COUNT(*) FROM tableName tn2 WHERE tn.[key] >= tn2.[key]) AS [Row#], ...
FROM tableName tn
ORDER BY [key]
JohnBPriceAuthor Commented:
Works great, but a couple changes are needed, the where clause must be included in both places, and the order by must be the primary key or the rank gets out of sequence.

SELECT Rank = (SELECT Count(*) From urTable b WHERE <YourWhereClause> a.PK > b.PK)+1,
Column1 ,Column2
FROM urTable a
WHERE <YourWhereClause>
I realize this is a dead topic, but I've found a much better solution that does not involve an expensive subquery. You can use the ROW_NUMBER() function.

For example:

SELECT ROW_NUMBER() OVER(ORDER BY PK) as RowNumber, PK, Column1, Column2 FROM urTable
JohnBPriceAuthor Commented:
Nice, but requires SQL Server 2005.  Not all my servers are there yet.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.