So next up, we're going to look at some of the window functions that are solely window functions,
unlike things like sum and average min max.
Those are aggregate functions.
You can use them with groups, you can use them as a window function.
The functions I'm going to show you in this video are uniquely window functions, so they include things
like rank.
Let's start with that one Rank is going to return the rank of the current row within its partition.
So let's take a look at how this works.
I will start with one of my previous queries and just duplicate that.
We still want employee number department salary.
And my goal here is to start by getting the overall rank for every employee based on their salary.
Who is the first salary?
Highest salary, second, highest, third, highest, and so on next to each row.
And so I would use rank to do that rank.
And then over we'll call this as overall salary rank and then instead of over, my SQL has no idea what
I'm trying to rank here.
It's just it doesn't know.
I need to specify that I'm going to order by salary and I'll do descending and you'll see what that
does.
So why don't we just run it and see what happens?
Here we are.
We see employee number, department salary and then their overall salary rank one here is for this salary
to this salary.
And that makes sense.
This is the highest salary.
We have 159,000.
This is the lowest salary.
We have 31,000.
A couple of things to note.
First of all, rank is not the same as row number, which is actually another one of these functions
that we have access to.
Row number will just give us a consecutive number of rows.
Right?
This is the fourth row, fifth row, sixth row.
But take a look at what happens here When we get to the seventh row.
We have a tie, 70,070 thousand when we use rank, Any values that are the same within a window are
considered the same rank.
And so then they skip a number following that.
So we have two sevens and then nine.
So it's not the same as just counting the rows if you have duplicate data or ties in your information.
So what we did right here is just calculate the rank for one massive window.
We didn't partition it so all the rows order them by salary and rank by salary.
But what we can also do is put a partition in there, so I'll say rank over and then partition by department.
In this time I'll also order by salary because that's what I care about.
Order by salary.
As department salary rank.
So what this will do is form up individual windows partitioned by department.
So there's, I think, three departments.
Right.
And then within each of those, it's going to order them by salary and calculate the rank for each row.
And this is what we get.
Let's look.
First of all, we have this person here in sales.
Oh, you know what?
I'm sorting the other way around.
It still works.
But you'll see that the highest paid person, hundred and 59,000 within sales is ranked seventh.
But that's because we are in us sending orders.
So let's also order by descending if we want the number one rank to be the highest paid, we could have
number one, be the lowest paid.
But I think most of the time you would want the first ranking to be the highest paid.
Okay, so here we are within their department.
This person here is ranked one within sales and also overall ranked one.
This employee here in engineering 103,000 is first in their department.
Second overall.
This person and engineering 91,000.
Their department rank is too, and their overall salary rank is three.
So another thing I should mention here is the order that we get back is something we could change.
We have more than one place we can order by.
We can order within each window, which is very different from the overall order at the end.
So if I wanted to order by employee number, I could do that order by department or order by the overall
salary rank, which is what's kind of happening right now.
But let's do an order by department, order by department.
This will just chunk together people from the same department.
As you can see here, all of our customer service people, now that department is up top and then all
the engineering people, all the sales people.
And I'm not suggesting this is better necessarily, but now we can see this person is first within customer
service at 61,000, but they're 11th overall.
This person is seventh within customer service in 21st overall.
So just an example of using rank.
This is a function that is only going to work with over.
It is only a window function.
We can't use it with group B and you don't have to partition just like these other functions we've seen.
If you don't partition it, we get one window with all of the rows and this will calculate in our case
based on salary the ranking for every row relative to all the other rows.
Whereas here we created different windows by partitioning by department, and then within each of those
small windows we order by salary and get the ranking.
And remember, ranking is not always consecutive.
We can have ties, in which case it will skip digits.
It's going to show us who's the third highest salary, but there might not be a fourth highest.
If there's people tied for third, it jumps to fifth.
I think that's just confusing to say out loud, though.
So that's rank.
We'll take a look at some more examples in the next video.