1
00:00:00,540 --> 00:00:03,320
Some functions are only
available as window functions,

2
00:00:03,320 --> 00:00:04,990
not as regular aggregates.

3
00:00:04,990 --> 00:00:08,590
This is because they are only useful in
the context of other records but not for

4
00:00:08,590 --> 00:00:10,040
the row itself.

5
00:00:10,040 --> 00:00:13,970
To answer our fourth question,
who is the top ranked player on each team?

6
00:00:13,970 --> 00:00:15,740
We'll use one of these functions.

7
00:00:15,740 --> 00:00:19,540
Rank, it gives the ranking of each row
within the window, starting with one.

8
00:00:20,670 --> 00:00:23,660
Windows specific functions order
the rows within the partition

9
00:00:23,660 --> 00:00:27,380
to determine whether one row
comes before or after another.

10
00:00:27,380 --> 00:00:28,690
The order clause,

11
00:00:28,690 --> 00:00:31,770
which is specified within
the parentheses after the partition,

12
00:00:31,770 --> 00:00:34,698
gives us the ability to define
the ordering of rows within the window.

13
00:00:34,698 --> 00:00:38,476
Let's use rank to find the top
ranked players in the tournament.

14
00:00:38,476 --> 00:00:42,332
We'll add a new field to the report
using this function, and

15
00:00:42,332 --> 00:00:44,079
then add the over keyword.

16
00:00:44,079 --> 00:00:45,009
This won't run yet

17
00:00:45,009 --> 00:00:48,512
because the function has no way of
determining how to rank the players.

18
00:00:48,512 --> 00:00:52,270
We might want to rank them alphabetically
by name or by number of matches one.

19
00:00:52,270 --> 00:00:55,992
To answer the question,
we want to order them by their ranking, so

20
00:00:55,992 --> 00:00:59,255
we need to specify that in the order for
the rank function.

21
00:00:59,255 --> 00:01:03,968
We'll introduce the clause inside
the parentheses with the words, order by,

22
00:01:03,968 --> 00:01:08,207
followed by the expression used to
determine order, player ranking.

23
00:01:10,408 --> 00:01:11,704
Like with partitions,

24
00:01:11,704 --> 00:01:15,654
you can pass multiple expressions to
order by clauses and you can order by

25
00:01:15,654 --> 00:01:20,178
aggregates as long as they return
one value per row of the result set.

26
00:01:20,178 --> 00:01:25,760
When we this ranks the players, but
it's the opposite of what we want.

27
00:01:25,760 --> 00:01:28,230
The lowest ranked players ranked first and

28
00:01:28,230 --> 00:01:31,040
the highest ranked players
have high rankings.

29
00:01:31,040 --> 00:01:34,460
We can easily reverse the order
by specifying a sort order for

30
00:01:34,460 --> 00:01:35,475
player ranking.

31
00:01:35,475 --> 00:01:38,235
The default is ASC or ascending.

32
00:01:38,235 --> 00:01:43,653
Since we want the rank to start with the
highest ranking, we'll specify DESC for

33
00:01:43,653 --> 00:01:53,130
descending This gives us the ranking for
each player across the entire tournament.

34
00:01:53,130 --> 00:01:55,120
We want to know the rank within the team,
so

35
00:01:55,120 --> 00:01:59,950
we'll add in a partition by player team so
that the ranking is done within each team.

36
00:01:59,950 --> 00:02:04,738
The partition clause goes inside the
parenthesis before the order by clause.

37
00:02:10,015 --> 00:02:13,130
When we run that, we get the ranking for
each player within their team.

38
00:02:14,490 --> 00:02:17,360
It's a little bit hard to see right now
because the records are still ordered

39
00:02:17,360 --> 00:02:22,460
by number of wins and that doesn't match
the order by player ranking exactly.

40
00:02:22,460 --> 00:02:23,380
Let's change that so

41
00:02:23,380 --> 00:02:26,560
that it's easier to see which players
are at the top of their team.

42
00:02:26,560 --> 00:02:29,060
To avoid typing the entire
window function again,

43
00:02:29,060 --> 00:02:31,530
we use the short cut where we
use the number of the field.

44
00:02:31,530 --> 00:02:35,160
In this case, our rank window
function is the 15th field.

45
00:02:35,160 --> 00:02:40,310
Right now we have DESC specified,
but we want to change that to AFC so

46
00:02:40,310 --> 00:02:42,220
that the number one ranked
player will be at the top.

47
00:02:43,810 --> 00:02:44,370
Let's run that.

48
00:02:45,760 --> 00:02:47,912
Now we can see that Xavier, Kate, and

49
00:02:47,912 --> 00:02:51,558
Gina are the highest ranked
players on their respective teams.

50
00:02:51,558 --> 00:02:55,314
There are other window functions which
provide information about the rows that

51
00:02:55,314 --> 00:02:58,454
appear before or after a particular row,
or determine the first or

52
00:02:58,454 --> 00:03:00,180
last value in a window.

53
00:03:00,180 --> 00:03:02,260
Check the teacher's notes for
more information on those.