T-SQL: NTile

Ranking Functions

There are several new impressive advancements in the latest version of t-sql; this article will focus on “Ranking” functions.

Prior to the release of SQL Server 2005 with its t-sql enhancements, working with blocks of related data was clunky at best. T-sql authors mostly relied on “GROUP BY” statements and then had to perform “CURSOR” and/or “SORT BY” acrobatics in order to return contiguous blocks of data in any meaningful manner.

Ranking functions overcome previous limitations to working with subsets of data within implicit groups.
In other words, SQL Server developers are now able to in essence, perform “GROUP BY” operations within existing groups.
We can achieve such actions by choosing from several segregation actions according to the desired result along with the familiar “SORT BY” finishing touches.

To begin these examples, first I’ll show you the source data.
It’s simply a series of records reflecting average data about people such as would be common to most databases .
For example, for all of our example will be working with the following fields: FirstName, Age, and Gender.

screen_rank_rawtable

NTILE()

In this final example, you can see how the sequencing may be determined by an entire subset as opposed to individual records within each group.
In this example, I’ve instructed the result set it may have only 3 “Age Groups.”
When the query is executed, each record is assigned one of the three groups according to its statistical relevance to other records within the returning result set.

screen_rank_ntile_code

screen_rank_ntile_results

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s