c# - returning rownumber() as part of the query -

i have sql query returned datatable dtstories (c#).

with records (select row_number() on (order pages.createdate desc)as 'row',  field1, field2...etc. records row between 1 , 7 

(there other instances i'll need "nth" row or between x & y rows, hence i'm not using simple select top 7 query.)

i trying different things based on row was, querying row[0] thinking have row number in. but, checking returns in sql server, returns field 1, field 2... etc why

foreach (datarow row in dtstories) {   if (row[0].tostring() == "1")   {      ...   } } 

doesn't work.

is there way either make sure row returned part of sqlquery datatable, or using c# access row1, row2 etc. of table?

you don't show whole select statement impossible know doing. can tell because ( , ) don't match.

i expect code (indented make clear) looks this:

with records  (    select row_number() on (order pages.createdate desc)as 'row',            field1, field2 -- ...    original_table ) select       field1, field2 -- ... records row between 1 , 7 

now should clear... include row column need in 2nd select list (outside cte)

with records  (    select row_number() on (order pages.createdate desc)as 'row',            field1, field2 -- ...    original_table ) select  row, -- here!     field1, field2 -- ... records row between 1 , 7