can we get the position of record while retriving records in sql server 2003

[sbv]

hi
i am using sql server 2003. can we get the position of record / row index in sql server while retrieving records?

like in some case we fetch records as per our criteria. And if that time need to know the postion of particular record then? how to get that?

plz help.

[cmhampton]

Are you using a cursor?

If so, you could always create a counter variable and increment it each time through the loop. Is this done through an application, or the query window? If it's done in a application, you may need to create a temporary table, insert the counter, plus whatever info you want, then select all the records from it after you are done with the fetch loop. If you are running it in a query window, you can just print the output.

Here's an example using the temp table

DECLARE @counter int;
      @counter = 0;

      CREATE TABLE #temp (counter int, otherdata varchar(255)) --Use whatever columns you need.

      DECLARE tempCursor CURSOR FOR SELECT * FROM table
      OPEN tempCursor

      DECLARE @value AS int --or whatever your datatype is

      FETCH NEXT FROM tempCursor INTO @value

      WHILE @@FETCH_STATUS = 0
      BEGIN

      INSERT #temp (counter, otherdata) VALUES (@counter, 'otherdata')
      @counter = @counter+1

      FETCH NEXT FROM tempCursor INTO @value
      END

      CLOSE tempCursor
      DEALLOCATE tempCursor

      SELECT * FROM #temp

      DROP TABLE #temp

It's probably not the best solution, but it would work.

[pty]

hi i am using sql server 2003. can we get the position of record / row index in sql server while retrieving records?

like in some case we fetch records as per our criteria. And if that time need to know the postion of particular record then? how to get that?

plz help.

A quick and dirty method would be to select into a temp table that has a identity column and then use that as the basis for your query. I wouldn't use this method if performance is high on your agenda

[sbv]

hi

thank you very much for your reply. But i dont want to run a loop for such thing and use a variable.
and as pty says using a identity column i.e a pk what if i select records as per my criteria.