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.