Next up, we're going to talk about the order by clause that we can include inside of over.
So order by inside of over.
It's very different than the the general order by we've already seen before.
They're similar in that they are going to change the order of rows.
But when we include order by between over between those parentheses, what we're telling my SQL to do
is to somehow change the order of the rows inside of each window.
So remember this example here.
Let's say that we did a partition by department within this.
I could then order by for each of these windows.
I don't know, order by employee number, order by salary.
And you might be surprised with the results that we get, even using some of the functions we've already
learned like average, why would we order, buy and then take the average?
Because the order has no impact on the average.
Right.
If we're going to sum everything up together and boil it down to a single number, why does it matter
what order they are in within the window?
And it doesn't if that's how average worked.
But what actually happens when we provide order by to certain aggregate functions like average and sum
is that we'll get a rolling average, a rolling sum.
I'll show you what that looks like.
And then additionally, I'm going to show you some special functions in the next video that use order
by that aren't aggregate functions.
They're not functions that we can use outside of a window function.
We have to use them with a window function and they're very different.
So we'll show you those later.
But for now, let's do a simple select employee number.
Let's just copy.
One of these here to save some time.
Employee number, department salary.
And then I'll do a sum and we'll sum the salary by department.
So how do we do that?
We do some salary over and then partition by department and we'll call that as department salary total
or something.
Okay.
Or maybe total department salary.
This is all review from previous videos.
Maybe still confusing, but no new concepts here.
We get every department customer service.
They all have the same summed up salary 326,000.
We see the individual salaries as well.
But now if I add a second column here, a second calculation that is very similar, but instead I'll
do it here.
I'm going to sum salary over partition by department and at the same time.
Ordered by salary.
And I'm going to call this rolling department salary and you'll see why when I run this, I need a comma.
So what this will do is still break up our all of the rows by department into three different windows
because we have three different departments.
But then within each of those windows it will order them by their salary and the default is ascending.
So I could go the other way around if I wanted to.
Maybe I should do descending to start, and then by ordering them within each of those windows, the
sum function behaves differently.
It's set up so that it will sum on a rolling basis.
This is what it looks like.
So every row here has an employee number department salary, and then we have the rolling department
salary in.
This changes for each one of these departments.
It starts at 61,000 because we have one employee that was some together, one salary.
Then our next employee is fixed 56,000.
So that gets added in and we get 117,000.
And then 55,000 added in.
So it is a rolling sum going one row at a time, whereas when we didn't order by, we simply got the
total sum by department, which we still get at the bottom here.
This is the total sum.
That's the total sum.
But we get different results for the previous rows leading up to that total sum.
It is a rolling sum and we could do the same thing with average get a rolling average.
I can of course change the order if I leave off descending.
Or it could put a sending in, but the default is ascending.
This is what we see.
We now go from this smallest salary in each one of these groups, so it starts at 31,000.
Then we sum on 38,000 up to the highest salary, which is 61,000.
Or if we look at engineering, 67,000 up to 103 versus if we looked at engineering before, this is
when I had descending, it was 103 down to 67.
So we can do this with some we can do this with I mean, you can even do it with Min and Max if we wanted
to, if I did something like.
Let's do a minimum salary over partition by department.
And we'll do an order by salary.
Like this.
And this is going to be silly.
But you'll see that it does work.
Let's give it a name as Rolling Min.
Okay.
When I run this one, here we are.
We have our salary for each row, and then the rolling min is not really rolling much.
It's always going to be the first row because of the order that I had them in.
Right?
Because we start with 31,000.
That's going to be the minimum for the customer service window.
And then 67,000 is the minimum for engineering.
So this is never updating.
Now if I sorted it the other way, ordered by salary, ascending or rather, what am I doing descending?
Run that.
Now the rolling min changes.
It started at 61,000.
And then our next row is 56,000.
Well, that's smaller.
So that's our new men and then 55,000.
And it keeps going until the end.
31,000.
Honestly, not useful or not very useful, but I just want to show that the behavior changes with these
aggregate functions when we are ordering within each window instead of just giving us the one sum of
everything added up or the one average of every row added or averaged within a window, it gives us
a rolling average, a rolling sum, a rolling min.
But what's more important is what happens with the other functions that we have yet to see that I'm
going to show you in the next video where ordering is really important.