lets consider a simple table
CREATE TABLE test
id int(11) NOT NULL default '0',
lastname varchar(30) NOT NULL default ''
and populate with following data.
Mysql provides a neat function where in you can limit the number of rows returned by the query.
So if you execute
select id,lastname from test limit 2;
the corresponding - one of the equivalent in sql server is:
select * from (select id,lastname,row_number() over(order by lastname) as rowcounter from test_limit) as temp where temp.rowcounter between 1 and 2