It's nice to know what the ANSI standard is, but what's a practical example?

The only time I use a limit-clause is to paginate. On the other hand, if I wanted to make sure to get all rows whose column value was between 1 and 3, even if it would be more than 3 rows, then I would use a where-clause:

  select * from table where id <= 3 order by id

not:

  select * from table order by id fetch first 3 rows with ties