Window Functions Part 3

After two or three weeks trying to resuscitate my laptop I finally made it, so here I go again.

Let’s continue exploring the different Window Function available on SQL Server. If you haven’t yet, I encourage you to take a look at Part 1 and Part 2 of the series.

Today, we’re going to be looking at three of the four ranking functions: RANK(), DENSE_RANK() and NTILE(), (ROW_NUMBER() is the fourth, but we already saw how that works). RANK() and DENSE_RANK() work very similar to ROW_NUMBER() since all of them assign a sequential number to each row in the result set, whereas NTILE() divides the result set creating “buckets” and assigning them a number for each bucket.

RANK() + DENSE_RANK()

To show you how these functions work, let’s create a query. We will use our already created Students table. Suppose you want to rank the students by grade, and you want to see your report ordered by the student who got the highest grade.

When you look closely to row 3, 4 and 5, you can spot the difference between RANK and DENSE_RANK, which is basically is that DENSE_RANK does not waste any numbers while RANK skips the numbers and continues. Let me put it in another way, ranking 3 was tied three times so, RANK will skip rank 4 and 5. If you look at row 10, it happens the same.

NTILE()

Let’s see an example on how NTILE works.  I’m not sure in other parts of the world, but at least here in Mexico, students with certain grade in a midterm exam may be exempted of their finals.

By using NTILE we can choose based on the grade, who will go home with nothing to worry but to what will be the next movie to watch on Netflix.

Since we want to know the next shift date, we use ShiftDate as an argument for the function. The 1 in there is how many rows ahead we want to see. The PARTITION BY is optional in the syntax, but if you recall the way partitioning works, you realize we use it because we want to divide the result set by EmployeeName and ShiftName. Remember that ORDER BY is required. If we compare both queries, they return the same results.

The way this function works is, it divides he data set in equal buckets. Here, I decided to divide the student into three groups and, the guys in group 1, will go home early. Depending on what you need, you can change the number of groups you want your data to be divided.

As I said in other posts, we’re not taking performance into consideration, we will talk about that later in the blog.

By the way, here’s the repo for the example we saw and you can find the others as well. https://github.com/learningsql/sql-code

I hope you guys liked the post, please share and like. See you soon, cheers!