WEBVTT 00:00:00.310 --> 00:00:01.640 Hi guys and welcome back! 00:00:01.640 --> 00:00:02.860 In this video we're going to learn 00:00:02.860 --> 00:00:04.940 how we can use the Order by Clause 00:00:04.940 --> 00:00:08.100 with a window function and why that can be useful. 00:00:08.100 --> 00:00:09.000 Let's get started. 00:00:09.970 --> 00:00:11.740 Here we've got the sample data 00:00:11.740 --> 00:00:14.650 for this project and what we have is essentially 00:00:14.650 --> 00:00:17.900 the polls that we have in our poll application. 00:00:17.900 --> 00:00:21.020 So, we've got the polls table with an ID title 00:00:21.020 --> 00:00:23.530 and the username, the options table 00:00:23.530 --> 00:00:26.500 with the ID, the option text and the poll ID. 00:00:26.500 --> 00:00:28.750 And we've got the votes table with the username, 00:00:28.750 --> 00:00:31.540 the option ID and of course that foreign key thing. 00:00:31.540 --> 00:00:33.230 Which is by the way not necessary 00:00:33.230 --> 00:00:36.570 I just included it here for the sake of example. 00:00:36.570 --> 00:00:39.800 In the polls table we've got three polls, 00:00:39.800 --> 00:00:42.090 the Flask versus Django, Python versus Java, 00:00:42.090 --> 00:00:44.910 Windows versus Mac and then we've got a few options 00:00:44.910 --> 00:00:47.620 for each and we've got a bunch of votes for each. 00:00:47.620 --> 00:00:49.720 We're not really worried about who voted 00:00:49.720 --> 00:00:51.000 for what or anything like that. 00:00:51.000 --> 00:00:54.950 What we want to do is we want to grab all the polls 00:00:55.810 --> 00:00:59.290 and we then want to rank them in order 00:00:59.290 --> 00:01:02.310 of how many votes they each got. 00:01:02.310 --> 00:01:04.100 So, the one with the most votes 00:01:04.100 --> 00:01:06.460 is gonna get the number one attached to it 00:01:06.460 --> 00:01:07.530 and the one with the least votes 00:01:07.530 --> 00:01:09.993 is gonna get the number three attached to it. 00:01:10.840 --> 00:01:12.870 We're gonna do that using window functions 00:01:12.870 --> 00:01:14.940 and first of all we have to go ahead 00:01:14.940 --> 00:01:17.660 and grab the poll titles as well as the vote counts. 00:01:17.660 --> 00:01:19.750 And we can do that without a window function. 00:01:19.750 --> 00:01:21.800 So, let me just type that out real quick. 00:01:24.090 --> 00:01:27.100 So, here we have the polls title being selected 00:01:27.100 --> 00:01:28.740 the count of votes as vote count 00:01:28.740 --> 00:01:30.900 and we're selecting from polls, left join on options, 00:01:30.900 --> 00:01:33.910 left join on votes and then we're grouping by, 00:01:33.910 --> 00:01:36.000 this should be polls.titles since 00:01:36.000 --> 00:01:38.220 it's what we're selecting up here. 00:01:38.220 --> 00:01:40.830 So, let me just run this and you'll see 00:01:40.830 --> 00:01:43.050 that all these rows get created, that's fine. 00:01:43.050 --> 00:01:46.890 And then we have our different polls in here. 00:01:46.890 --> 00:01:50.710 What we got is each poll and how many votes they have. 00:01:50.710 --> 00:01:53.665 So, we can verify this just by looking into the votes 00:01:53.665 --> 00:01:56.640 but of course, we are comfortable enough at this point 00:01:56.640 --> 00:01:59.930 to realise that this is probably gonna be correct. 00:01:59.930 --> 00:02:02.210 So, now that we've got this 00:02:02.210 --> 00:02:03.810 We do have to think about how we're going to select 00:02:03.810 --> 00:02:06.610 one more column so it's probably gonna be here 00:02:06.610 --> 00:02:09.380 in the select part of the query. 00:02:09.380 --> 00:02:12.430 We want to add one more column and we can use 00:02:12.430 --> 00:02:15.830 the rank function to do that. 00:02:15.830 --> 00:02:19.651 However, remember that when we run something here 00:02:19.651 --> 00:02:24.010 that only has access to the current row. 00:02:24.010 --> 00:02:27.150 So, therefore if we have access to the current row 00:02:27.150 --> 00:02:30.380 we cannot possibly rank it against other rows, 00:02:30.380 --> 00:02:32.380 we just don't have that data. 00:02:32.380 --> 00:02:36.270 That's why the rank function requires a window 00:02:36.270 --> 00:02:39.060 so, we do rank over. 00:02:39.060 --> 00:02:41.960 If we run this now, you're going to see 00:02:41.960 --> 00:02:43.930 that we end up with the rank. 00:02:43.930 --> 00:02:46.900 So, we've got rank one, rank one, rank one. 00:02:46.900 --> 00:02:49.210 Obviously, not what we wanted but nonetheless 00:02:49.210 --> 00:02:51.010 we've added a number there. 00:02:51.010 --> 00:02:53.730 The reason why we're getting rank one for everything 00:02:53.730 --> 00:02:56.830 is because we haven't told the rank over 00:02:56.830 --> 00:02:59.350 what it should rank by. 00:02:59.350 --> 00:03:01.960 Is it the title, is it the vote count, 00:03:01.960 --> 00:03:03.660 is it something else potentially. 00:03:03.660 --> 00:03:05.800 And since it doesn't know, then every row 00:03:05.800 --> 00:03:08.970 gets the same rank because there's nothing to rank by. 00:03:08.970 --> 00:03:13.110 So, we need to give it a column to sort by. 00:03:13.110 --> 00:03:16.950 And the way we do that is we do order by count 00:03:16.950 --> 00:03:20.386 of votes and we're gonna do descending order. 00:03:20.386 --> 00:03:24.670 What this tells the rank function is that the thing 00:03:24.670 --> 00:03:29.090 with the most values in this count of votes 00:03:29.090 --> 00:03:31.800 is gonna get the first number. 00:03:31.800 --> 00:03:32.860 And then it's gonna move over 00:03:32.860 --> 00:03:35.010 to the next number and so on. 00:03:35.010 --> 00:03:38.780 So, now if we run this, you'll see that we get 00:03:38.780 --> 00:03:41.470 ranks one, two, and three. 00:03:41.470 --> 00:03:43.880 Note that the order by here in the window function 00:03:43.880 --> 00:03:47.720 is completely separate from what we can include down here. 00:03:47.720 --> 00:03:50.840 So, we could order by vote counting ascending order 00:03:50.840 --> 00:03:53.350 for example and that would sort by this column, 00:03:53.350 --> 00:03:57.030 the final results but this here only effects the order 00:03:57.030 --> 00:04:00.460 in which the window is evaluated for ranking purposes. 00:04:00.460 --> 00:04:03.050 If we run this, you'll see that we get the vote 00:04:03.050 --> 00:04:05.450 count of two first with a rank of three. 00:04:05.450 --> 00:04:08.863 So, these are separated and evaluated individually. 00:04:09.880 --> 00:04:11.110 Thank you guys for watching this video. 00:04:11.110 --> 00:04:11.943 I hope you've learned something 00:04:11.943 --> 00:04:13.913 and I'll see you in the next one.