limit and order by

lets consider a simple table

CREATE TABLE test
(                            
  id int(11) NOT NULL default '0',                   
  lastname varchar(30) NOT NULL default ''           
) ENGINE=MyISAM;

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;

will return 2 rows. but is it the expected result? For the expected result an order by clause is required .

for eg. in the above scenario, by adding order by lastname clause with limit, the user can control the order in which mysql limit the number of row.

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