asked
how to find the absolute position of a record
i have a requirement like, in the application i have two buttons called PREV AND NEXT buttons and i am passing a parameter like date. suppose if i give the date as '11/20/2010' on the date it will list out all the rows. suppose from the application if i press PREVIOUS button...it should go to previous record from the current record...if i press NEXT it should go to NEXT record from the current record.
Are you after a single snapshot of the data, and to work your way through it, or are you thinking of an app that checks the database between refreshes?
**EDIT** I see now that what you're actually asking for is ONE record. The procedure will handle that too, pass @pagesize=1. **END EDIT** Untested, but I think this will work. CREATE PROC GetPage(@d date, @page int=1, @pagesize int=10) AS WITH PagingTable AS( SELECT col1, col2, col3, ROW_NUMBER() OVER(ORDER BY col1) As RowNum FROM some_table WHERE some_date_column=@dt) SELECT * FROM PagingTable WHERE RowNum BETWEEN (@page-1)*@pagesize +1 AND @page*@pagesize ORDER BY RowNum
