1
00:00:00,150 --> 00:00:05,220
Next up, we're going to talk about the order by clause that we can include inside of over.

2
00:00:05,220 --> 00:00:07,530
So order by inside of over.

3
00:00:07,530 --> 00:00:11,550
It's very different than the the general order by we've already seen before.

4
00:00:11,580 --> 00:00:15,060
They're similar in that they are going to change the order of rows.

5
00:00:15,060 --> 00:00:21,600
But when we include order by between over between those parentheses, what we're telling my SQL to do

6
00:00:21,600 --> 00:00:26,670
is to somehow change the order of the rows inside of each window.

7
00:00:27,000 --> 00:00:29,400
So remember this example here.

8
00:00:29,400 --> 00:00:33,000
Let's say that we did a partition by department within this.

9
00:00:33,000 --> 00:00:36,210
I could then order by for each of these windows.

10
00:00:36,210 --> 00:00:39,240
I don't know, order by employee number, order by salary.

11
00:00:39,240 --> 00:00:45,600
And you might be surprised with the results that we get, even using some of the functions we've already

12
00:00:45,600 --> 00:00:50,340
learned like average, why would we order, buy and then take the average?

13
00:00:50,340 --> 00:00:52,650
Because the order has no impact on the average.

14
00:00:52,650 --> 00:00:52,800
Right.

15
00:00:52,800 --> 00:00:57,930
If we're going to sum everything up together and boil it down to a single number, why does it matter

16
00:00:57,930 --> 00:01:00,600
what order they are in within the window?

17
00:01:00,870 --> 00:01:04,110
And it doesn't if that's how average worked.

18
00:01:04,110 --> 00:01:09,930
But what actually happens when we provide order by to certain aggregate functions like average and sum

19
00:01:09,930 --> 00:01:12,960
is that we'll get a rolling average, a rolling sum.

20
00:01:12,990 --> 00:01:14,580
I'll show you what that looks like.

21
00:01:14,580 --> 00:01:19,650
And then additionally, I'm going to show you some special functions in the next video that use order

22
00:01:19,650 --> 00:01:21,900
by that aren't aggregate functions.

23
00:01:21,900 --> 00:01:25,320
They're not functions that we can use outside of a window function.

24
00:01:25,410 --> 00:01:28,920
We have to use them with a window function and they're very different.

25
00:01:28,920 --> 00:01:30,540
So we'll show you those later.

26
00:01:30,540 --> 00:01:35,250
But for now, let's do a simple select employee number.

27
00:01:35,250 --> 00:01:36,270
Let's just copy.

28
00:01:37,160 --> 00:01:39,950
One of these here to save some time.

29
00:01:40,670 --> 00:01:42,350
Employee number, department salary.

30
00:01:42,350 --> 00:01:48,840
And then I'll do a sum and we'll sum the salary by department.

31
00:01:48,840 --> 00:01:49,990
So how do we do that?

32
00:01:50,000 --> 00:02:00,770
We do some salary over and then partition by department and we'll call that as department salary total

33
00:02:00,770 --> 00:02:01,460
or something.

34
00:02:02,450 --> 00:02:02,820
Okay.

35
00:02:02,840 --> 00:02:04,730
Or maybe total department salary.

36
00:02:07,830 --> 00:02:10,470
This is all review from previous videos.

37
00:02:10,889 --> 00:02:14,130
Maybe still confusing, but no new concepts here.

38
00:02:14,280 --> 00:02:17,730
We get every department customer service.

39
00:02:17,730 --> 00:02:21,690
They all have the same summed up salary 326,000.

40
00:02:22,260 --> 00:02:24,390
We see the individual salaries as well.

41
00:02:24,870 --> 00:02:32,520
But now if I add a second column here, a second calculation that is very similar, but instead I'll

42
00:02:32,520 --> 00:02:33,570
do it here.

43
00:02:33,930 --> 00:02:38,880
I'm going to sum salary over partition by department and at the same time.

44
00:02:39,840 --> 00:02:41,970
Ordered by salary.

45
00:02:42,390 --> 00:02:49,830
And I'm going to call this rolling department salary and you'll see why when I run this, I need a comma.

46
00:02:51,210 --> 00:02:57,930
So what this will do is still break up our all of the rows by department into three different windows

47
00:02:57,930 --> 00:02:59,460
because we have three different departments.

48
00:02:59,460 --> 00:03:06,150
But then within each of those windows it will order them by their salary and the default is ascending.

49
00:03:06,600 --> 00:03:08,640
So I could go the other way around if I wanted to.

50
00:03:08,670 --> 00:03:15,780
Maybe I should do descending to start, and then by ordering them within each of those windows, the

51
00:03:15,780 --> 00:03:18,200
sum function behaves differently.

52
00:03:18,210 --> 00:03:22,250
It's set up so that it will sum on a rolling basis.

53
00:03:22,260 --> 00:03:23,490
This is what it looks like.

54
00:03:24,390 --> 00:03:30,030
So every row here has an employee number department salary, and then we have the rolling department

55
00:03:30,030 --> 00:03:30,600
salary in.

56
00:03:30,600 --> 00:03:33,210
This changes for each one of these departments.

57
00:03:33,390 --> 00:03:39,630
It starts at 61,000 because we have one employee that was some together, one salary.

58
00:03:39,780 --> 00:03:42,210
Then our next employee is fixed 56,000.

59
00:03:42,210 --> 00:03:45,450
So that gets added in and we get 117,000.

60
00:03:46,170 --> 00:03:47,940
And then 55,000 added in.

61
00:03:47,940 --> 00:03:55,260
So it is a rolling sum going one row at a time, whereas when we didn't order by, we simply got the

62
00:03:55,260 --> 00:03:58,920
total sum by department, which we still get at the bottom here.

63
00:03:59,130 --> 00:04:00,300
This is the total sum.

64
00:04:00,300 --> 00:04:01,380
That's the total sum.

65
00:04:01,380 --> 00:04:06,270
But we get different results for the previous rows leading up to that total sum.

66
00:04:06,270 --> 00:04:10,740
It is a rolling sum and we could do the same thing with average get a rolling average.

67
00:04:10,740 --> 00:04:13,980
I can of course change the order if I leave off descending.

68
00:04:14,990 --> 00:04:17,790
Or it could put a sending in, but the default is ascending.

69
00:04:17,810 --> 00:04:18,899
This is what we see.

70
00:04:18,920 --> 00:04:24,740
We now go from this smallest salary in each one of these groups, so it starts at 31,000.

71
00:04:24,740 --> 00:04:29,090
Then we sum on 38,000 up to the highest salary, which is 61,000.

72
00:04:29,090 --> 00:04:35,930
Or if we look at engineering, 67,000 up to 103 versus if we looked at engineering before, this is

73
00:04:35,930 --> 00:04:39,350
when I had descending, it was 103 down to 67.

74
00:04:40,490 --> 00:04:45,860
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

75
00:04:45,860 --> 00:04:48,650
to, if I did something like.

76
00:04:50,520 --> 00:05:00,150
Let's do a minimum salary over partition by department.

77
00:05:01,120 --> 00:05:05,860
And we'll do an order by salary.

78
00:05:07,040 --> 00:05:08,060
Like this.

79
00:05:08,760 --> 00:05:10,800
And this is going to be silly.

80
00:05:10,890 --> 00:05:12,810
But you'll see that it does work.

81
00:05:13,800 --> 00:05:18,090
Let's give it a name as Rolling Min.

82
00:05:19,660 --> 00:05:20,220
Okay.

83
00:05:20,350 --> 00:05:23,230
When I run this one, here we are.

84
00:05:23,260 --> 00:05:29,200
We have our salary for each row, and then the rolling min is not really rolling much.

85
00:05:29,200 --> 00:05:33,310
It's always going to be the first row because of the order that I had them in.

86
00:05:33,760 --> 00:05:34,090
Right?

87
00:05:34,090 --> 00:05:35,590
Because we start with 31,000.

88
00:05:35,590 --> 00:05:39,310
That's going to be the minimum for the customer service window.

89
00:05:39,310 --> 00:05:42,370
And then 67,000 is the minimum for engineering.

90
00:05:42,370 --> 00:05:44,110
So this is never updating.

91
00:05:44,110 --> 00:05:51,580
Now if I sorted it the other way, ordered by salary, ascending or rather, what am I doing descending?

92
00:05:54,210 --> 00:05:55,200
Run that.

93
00:05:57,240 --> 00:05:59,060
Now the rolling min changes.

94
00:05:59,070 --> 00:06:01,500
It started at 61,000.

95
00:06:01,710 --> 00:06:04,010
And then our next row is 56,000.

96
00:06:04,020 --> 00:06:04,920
Well, that's smaller.

97
00:06:04,920 --> 00:06:07,680
So that's our new men and then 55,000.

98
00:06:07,680 --> 00:06:09,450
And it keeps going until the end.

99
00:06:09,450 --> 00:06:10,440
31,000.

100
00:06:10,470 --> 00:06:16,380
Honestly, not useful or not very useful, but I just want to show that the behavior changes with these

101
00:06:16,380 --> 00:06:23,310
aggregate functions when we are ordering within each window instead of just giving us the one sum of

102
00:06:23,310 --> 00:06:29,100
everything added up or the one average of every row added or averaged within a window, it gives us

103
00:06:29,100 --> 00:06:32,310
a rolling average, a rolling sum, a rolling min.

104
00:06:32,490 --> 00:06:37,350
But what's more important is what happens with the other functions that we have yet to see that I'm

105
00:06:37,350 --> 00:06:41,580
going to show you in the next video where ordering is really important.