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
i开发者_开发技巧ndexOf(select id from t where date = '2/4/2009') = 3
I think it has something to do with ROW_Number() but not really sure...
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
Edit
select * from (select ROW_NUMBER() over ( order by id) -1 as Index,* from table) a
where --condition
精彩评论