If you have primary key in your table, then have a look at the following script
SELECT (SELECT COUNT(i.au_id)
FROM pubs..authors i
WHERE i.au_id >= o.au_id ) AS RowID,
au_fname + ' ' + au_lname AS 'Author name'
FROM pubs..authors o
ORDER BY RowID
This will generate the RowId and then accordingly you can select the rows which you want sepcifying the range in the where clause.
if you are using SQL Server 2005 then have a look at following link.