How to Use Limited FETCH in SQL
Whenever the ISO/IEC SQL standard is changed, it is usually to expand the capabilities of the language. This is a good thing. However, sometimes when you make such a change you cannot anticipate all the possible consequences. This happened with the addition of limited FETCH capability in SQL:2008.
The idea of the limited FETCH is that although a SELECT statement may return an indeterminate number of rows, perhaps you care only about the top three or perhaps the top ten. Pursuant to this idea, SQL:2008 added syntax shown in the following example:
SELECT Salesperson, AVG(TotalSale) FROM SALES GROUP BY Salesperson ORDER BY AVG(TotalSale) DESC FETCH FIRST 3 ROWS ONLY;
That looks fine. You want to see who your top three salespeople are in terms of those who are selling mostly high priced products. However, there is a small problem with this. What if three people are tied with the same average total sale, below the top two salespeople? Only one of those three will be returned. Which one? It is indeterminate.
Indeterminacy is intolerable to any self-respecting database person so this situation was corrected in SQL:2011. New syntax was added to include ties, in this manner:
SELECT Salesperson, AVG(TotalSale) FROM SALES GROUP BY Salesperson ORDER BY AVG(TotalSale) DESC FETCH FIRST 3 ROWS WITH TIES;
Now the result is completely determined: If there is a tie, you get all the tied rows. As before, if you leave off the WITH TIES modifier, the result is indeterminate.
A couple of additional enhancements were made to the limited FETCH capability in SQL:2011.
First, percentages are handled, as well as just a specific number of rows. Consider the following example:
SELECT Salesperson, AVG(TotalSale) FROM SALES GROUP BY Salesperson ORDER BY AVG(TotalSale) DESC FETCH FIRST 10 PERCENT ROWS ONLY;
It’s conceivable that there might be a problem with ties when dealing with percentages, just as there is with a simple number of records, so the WITH TIES syntax may also be used here. You can include ties or not, depending on what you want in any particular situation.
Second, suppose you don’t want the top three or the top ten percent, but instead want the second three or second ten percent? Perhaps you want to skip directly to some point deep in the result set. SQL:2011 covers this situation also. The code would be similar to this:
SELECT Salesperson, AVG(TotalSale) FROM SALES GROUP BY Salesperson ORDER BY AVG(TotalSale) DESC OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;
The OFFSET keyword tells how many rows to skip before fetching. The NEXT keyword specifies that the rows to be fetched are the ones immediately following the offset. Now the salespeople with the fourth, fifth, and sixth highest average sale total is returned.
As you can see, without the WITH TIES syntax, there is still an indeterminacy problem. If the third, fourth, and fifth salespeople are tied, it is indeterminate which two will be included in this second batch and which one will have been included in the first batch.
It may be best to avoid using the limited FETCH capability. It’s too likely to deliver misleading results.