Next up, we'll look at a few more of these window only functions We saw rank and very similar to rank.
We have row number.
The main difference here is that rank allows for ties as we discussed.
You can have multiple things ranked one if they have the same value.
Multiple things can be ranked four as we see here because they have the same value.
And so we end up skipping numbers potentially.
Then we have row number, which is just going to label each row within a partition, starting with one
and going sequentially one, two, three, four, five, six, seven, counting the rows.
So it doesn't care if the values have a tie or anything, it just counts the rows so it doesn't even
look at the values.
I'll show you an example of that.
If we just did alongside the department salary rank, I'll also do let me just highlight an example
here.
Like within sales, right?
We have the highest ranked in sales, 160,000, second, third and then fourth rank is a tie, 61,060
1000.
And then that just jumps to sixth rank and seventh ranked.
So if I do the same thing except instead of rank, I will do row number.
Partitioned by department, order by salary.
We set up our different windows, we order them how we want, and then we just say, number them in
order and I'll go with department salary.
I don't even.
Yeah, I don't know.
Just row number.
Sure.
It's simple enough.
And what did I.
Am I missing a comma somewhere?
Oh, duh.
I can't use rule number as a name there, because that is the name of a function.
So let's call it department row number.
And here we go.
So let's take a look again at sales.
This is our ranking here.
One, two, three, four, four, six, seven.
And this is our row number here.
One, two, three, four, five, six, seven.
All right, So that's row number.
We also have another thing that's somewhat confusing, which is dense rank.
Dense rank is like rank returns to rank at the current row within its partition without gaps.
So when we have peers that are ties, they get the same rank, but then the function will assign consecutive
ranks to peer groups, meaning it won't skip any values.
So this is really a minor detail.
I mean, it's important, but it's not that essential that you know this.
But I'll show you what that looks like.
Let's do the overall salary rank here and then rank.
Dent rank, dent rank over order by salary as overall salary dense rank.
It's a long column named Let's see if it all fits in our screen.
I think it would have to zoom out.
Or maybe we can just rename this a tiny bit like we'll call this overall dense rank.
And this is overall rank.
So let's run this paste you in here.
Yeah, I have to zoom out.
Okay, here we are.
Overall rank.
Let's order by that so you can see order by overall rank.
They'll make it easier to verify what's happening.
So we have first, second, third, fourth, fifth, sixth, seventh.
We have a tie, right?
And then after seventh, they're both 70,000.
After seven, we have ninth rank and then ten and we have a tie for 11, 11, 11, 11.
We have three employees with 61,000 salaries, and then it jumps right to 14th after that.
And if we look at dense rank instead.
One, two, three, four, five, six.
There's a tie at seven.
So two people have that salary again, same deal.
But then it resumes counting the rank at eight.
So it doesn't skip any places like regular rank does.
So we have rank versus dense rank.
The highest dense rake in this example is 18th place versus 21st here for regular rank.
And then we have row number, which right now we're using on a department basis.
So it's hard to compare apples to apples.
But if I also add it in overall row number.
We'll do Roe.
Underscore number over order by salary as overall number.
Let's just call that overall number or num.
I'm going to have to zoom out even further.
These column names are getting long.
Zoom, zoom, zoom.
You'll see it goes up to 21 as the overall number, all consecutive, just counting each row.
Overall rank goes up to 21, but it skips some numbers whenever there's a tie.
And then overall dense rank does not skip any numbers if it has a tie.
They share that rank and then it just resumes with the next consecutive number, meaning we only have
18 as the highest instead of 21.
All right.
That's probably more than you ever want to know about.
Rankings and row numbers and dense rankings.
Next, we have a few more of these window functions I want to cover.