How do I limit the number of rows returned in my resultset?

Often you will have a huge resultset, but you only care about the first 5, 10 or 20 rows. There are several mechanisms to do this in both SQL Server and Access. 
 
For these examples, let's assume you only want to retrieve the most recent 10 rows from a table called foo, with a datetime column called dt. 
 
From the database side 
 
For MS Access or SQL Server 7.0/2000, you can use TOP n: 
 

SELECT TOP 5 column1, column2, dt FROM foo ORDER BY dt DESC

 
For SQL Server 6.5, you'll have to take a slightly different approach, utilizing the SET ROWCOUNT functionality (this will not work in Access): 
 

SET ROWCOUNT 5  SELECT column1, column2, dt FROM foo ORDER BY dt DESC  SET ROWCOUNT 0 -- (This last step is the most important!)

 
From the ASP side 
 
You could do this from ASP in a couple of ways, though I really don't recommend either method. Reason being, you're making the database do all the work of pumping an entire table when you're not interested in all of the rows. 
 
The first method would simply be to maintain a counter, and when you get to your count, stop the loop. 
 

<%      ' ...      sql = "SELECT column1, column2, dt FROM foo ORDER BY dt DESC"      set rs = conn.execute(sql)      counter = 1      do while not rs.eof and counter < 5          ' process rs(???)          counter = counter + 1          rs.movenext      loop      ' ...  %>

 
Another method is to use the MaxRecords method of the ADODB.Recordset object. If you've been through aspfaq.com in the past, you'll probably guess that this is my least favorite method. 
 

<%      ' ...      sql = "SELECT column1, column2, dt FROM foo ORDER BY dt DESC"      set rs = CreateObject("ADODB.Recordset")      rs.MaxRecords = 5      rs.open sql, conn      do while not rs.eof          ' process rs(???)          rs.movenext      loop      ' ...  %>