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

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.

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

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.

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

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

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

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.