variable increment in select statement

[ronaldmacdonald]

Im trying to increment a variable in a select statement
such as

declare @cnt as int

select field1, field2,(select @cnt = @cnt + 1)
from A_TABLE

but Im getting an "incorrect syntax error near =" message. Ive tried various combinations to no avail. Is this possible?

 

[dbomrrsm]

SELECT
(SELECT COUNT (*) FROM A_TABLE A
WHERE A.FIELD1 <= B.FIELD1) INCREMENT, *
FROM A_TABLE B
ORDER BY INCREMENT

[blue]DBomrrsm[/blue]

 

[ronaldmacdonald]

Needs unique values in EVERY row being selected against.

 

[dbomrrsm]

Try this:

How Do I Get a Row Number On Each Row of a Query Result Set?
faq183-3021

[blue]DBomrrsm[/blue]