How can I tell a database to only return data in discrete portions?

I couldn't find a proper discussion thread on this topic, so I'm going to go ahead and ask here.

Problem: I have a select query that returns a result of size 100,000+. The user wants to view all this data, but obviously I can't give it to him all at once. I also don't want to store so much data on the client's memory. I want the user to be able to "page through" the results, being able to view the data in pages of 500 records.

So, how can I ask the database to only send me back 500 records at a time?

From stackoverflow

• You can use a combination of rownumber and top (atleast in SQL server)

• This depends on the database you are using, but in MySql you could try something like:

  SELECT * FROM MyTable LIMIT <start>, 500
  

  and replace <start> with the index you would like to start on (e.g. 0 for the first page, 501 for the second page).