When is the proper time to use the SQL statement "SELECT [results] FROM [tables] WHERE [conditions] FETCH FIRST [n] ROWS ONLY"

I'm not quite sure when selecting only a certain number of rows would be better than simply making a more specific select statement. I have a feeling I'm missing something pretty straight forward but I can't figure it out. I have less than 6 months experience with any SQL and it's been cursory at that so I'm sorry if this is a really simple question but I couldn't find a clear answer.

From stackoverflow

• Custom paging, typically.

• When you want to display the values to a user, you are only likely to need N rows. Generally the database server can fetch the first N rows, faster than it can fetch all of the rows, so your screen redraw can go a little bit faster.

  Oracle even has a hint, called FIRST_ROWS that suggests that getting data back quick, is more important that getting it all back efficiently.

• We're using the statement for the following reasons:

  1. Show only the most relevant results (say the top 100) without having to transfer all rows from the DB to the client. In this case, we also use ORDER BY.

  2. We just want to know if there are matching rows and have a few examples. In this case, we don't order the results and again, FETCH FIRST is much more cheap than having the DB prepare to transfer lots of rows and then throw them away at the client. This is usually during software development when need to get a feeling if a certain SQL is right.

• The designers of SQL agree with you, which is why standard SQL doesn't included top/limit/fetch first, etc.

• Think google search results and the number of pages there are typically for results.

  Though obviously, there's much more to it in their case but that's the idea.

• I know of two common uses:

  Paging : be sure to specify an ordering. If an ordering isn't specified, many db implementations use whatever is convenient for executing the query. This "optimal" ordering behavior could give very unpredictable results.

  SELECT top 10 CustomerName
  FROM Customer
  WHERE CustomerID > 200 --start of page
  ORDER BY CustomerID
  

  Subqueries : many places that a subquery can be issued require that the result is a single value. top 1 is just faster than max in many cases.

  --give me some customer that ordered today
  SELECT CustomerName
  FROM Customer
  WHERE CustomerID =
  (
    SELECT top 1 CustomerID
    FROM Orders
    WHERE OrderDate = @Today
  )
  

• Aside from paging, any time you want the most or least [insert metric here] row from a table, ordering on [whatever metric] and limiting to 1 row is, IME, better than doing a subquery using MIN/MAX. Could vary by engine.