Posted by Trevor Best on 10/25/05 14:16

Elham Ghoddousi wrote:
> Hi
> How can i have Row number in my SELECT Statement ?

Question is, why do you want one?

It's meaning would only be valid for the one time you run a query, the
next time you run it, it may change. That said, if you must have one
then... (I must have thought so at one point otherwise that link below
wouldn't exist :-))

If you're ordering by a unique key then this would work:

Select KeyCol, col1,col2,(select count(*) from Table1 a where a.KeyCol
<= Table1.Keycol) As RowNumber from Table1 order by KeyCol;

If you'ree using Access as the front-end, this may help:
http://www.trevor.easynet.co.uk/AccFAQ/queries.htm#counter

The Access one is regardless of ordering but there is a caveat with it.

[Back to original message]