How can I finding the index of a record with SQL or Stored Procedure?

Question

Using SQL Server 2008, but could relate to other databases as well probably.

If I had the following data (table t):

id text  date
1 Data1 2/1/2009
2 Data2 2/2/2009
3 Data3 2/3/2009
4 Data4 2/4/2009
5 Data5 2/5/2009

How could I find the index of a certain record?

indexOf(select id from t where id = 1) = 0

or

indexOf(select id from t where date = '2/4/2009') = 3

I think it has something to do with ROW_Number() but not really sure...

Answer 1

Edit
select * from (select ROW_NUMBER() over ( order by id) -1 as Index,* from table) a where --condition

Answer 2

Yes, ROW_NUMBER is exactly what you want to leverage along with the OVER clause. You can accomplish what you are trying to do using a sub-query or a CTE such as this:

;WITH Q as (
    SELECT id, text, date, ROW_NUMBER() OVER (ORDER BY date) as RowNumber 
    FROM t
)
SELECT * FROM Q WHERE RowNumber = 3