Ranking Functions – Top 5
A friend of mine recently told me she’s very proud of her son, because he is consistently in the upper quarter of every class he takes. Right there she performed a calculation similar to the NTILE function. She didn’t know it, but she tiled the class into four pieces and then identified which piece her son belongs in.
Just like its three fellow ranking functions, NTILE takes data you’ve sorted and then marches straight down the list in order of row number. Instead of assigning a rank value to each record, NTILE counts records and assigns them into a group (a.k.a., “tile”, or “bucket” in geek speak) based on:
1) the number of tiles/pieces you have specified, and
2) the count of records contained in the query.
Like all other ranking functions, we want to first write a SELECT statement and an ORDER BY clause to determine the sort order.
Adding the NTILE function to the query is almost the same as with the first three ranking functions we saw from yesterdays blog post. The ORDER BY clause gets plugged into the parentheses of the OVER( ) clause. Just one new item to note with the NTILE: you must insert the number of tiles (or pieces) in the parentheses of the NTILE. In this case we want four pieces, just like my friend’s story about her son.
The upper quarter consists of Grants 007, 008, 009 and are the “1” group of records. The lowest quarter (the group “4” records) consists of Grants 001 and 010 as seen in the figure below
If you want just an upper and lower half ranking just change this query to show just two pieces. We see the grant records divided into an upper half (the “1” records) and a lower half (“2” records). The figure below shows the top 5 grants in group 1 and the lowest 5 grants in group 2. In this case the NTILE query now divides the grants into an upper half and lower half.