How to determine order number of an item in sorted table

Question

(continued from http://stackoverflow.com/questions/3707200/read-n-consequent-items-by-activerecord)

My project is C# and uses CastleProject ActiveRecord on MS SQL Server database.

I need to understand the order number of an item from a database, if some sorting criteria applied.

Example: I have a lot of messages in some table, where message has ID, time and text. I'd like to understand which number particular message will have, if I sort messages by time. Next time, I'd like to get order number of the same message when sort by text applied.

Is it possible to achieve with ActiveRecord means or only with stored procedures on SQL Server?

Answer 1

You need to use the ROW_NUMBER() function. Check this link:

http://www.singingeels.com/Articles/Pagination_In_SQL_Server_2005.aspx

It works the same for SQL Server 2008, so don't worry.