SQL select single row number x only

For processing SQL data in VBA I want to use a loop (For…Next) that reads line by line from a SQL database. As records were deleted the ID column has gaps. The table looks for example like this:

ID   Value
1    Peter
3    Paul
4    Mary
9    George

Having four lines I would like to run

For i = 1 to 4
   SELECT ???
Next i

of course a “For each” would do good, too.

Answer

If you want 4 rows, then use order by and top – or a fetch clause:

select top(4) t.*
from mytable t
order by id

Or:

select t.*
from mytable t
order by id
offset 0 rows fetch first 4 rows only

You can then iterate through the resultset in your application.

If, on the other hand, you want the 4th row only, then just change the fetch clause:

select t.*
from mytable t
order by id
offset 3 rows fetch next 1 row only