WEBVTT 1 00:00:00.290 --> 00:00:01.570 Hi guys, and welcome back. 2 00:00:01.570 --> 00:00:03.210 In this video, I wanted to tell you about 3 00:00:03.210 --> 00:00:07.440 the DISTINCT keyword in Postgres and how we can use it. 4 00:00:07.440 --> 00:00:09.150 As an example, for this lecture, 5 00:00:09.150 --> 00:00:13.720 we're gonna try to get the most voted option for each poll. 6 00:00:13.720 --> 00:00:16.470 And we're gonna show you the example data first. 7 00:00:16.470 --> 00:00:18.450 So here we've got what our data is gonna look like. 8 00:00:18.450 --> 00:00:19.830 We've got some polls, 9 00:00:19.830 --> 00:00:20.870 two of them at the moment, 10 00:00:20.870 --> 00:00:22.250 Flask versus Django, 11 00:00:22.250 --> 00:00:24.390 PostgreSQL versus MySQL, 12 00:00:24.390 --> 00:00:27.750 we've got five options three for poll One, 13 00:00:27.750 --> 00:00:29.830 and two for poll two. 14 00:00:29.830 --> 00:00:31.620 And we have ourselves some votes. 15 00:00:31.620 --> 00:00:33.270 So here's three different users 16 00:00:33.270 --> 00:00:35.890 voting on these options differently. 17 00:00:35.890 --> 00:00:37.330 Now, as well as this, 18 00:00:37.330 --> 00:00:40.200 we're gonna have some sections down here for the query 19 00:00:40.200 --> 00:00:41.740 that we're gonna run on the left. 20 00:00:41.740 --> 00:00:43.050 And the output that we're gonna get 21 00:00:43.050 --> 00:00:45.660 if we run that query on the right. 22 00:00:45.660 --> 00:00:46.960 We're gonna start with a query 23 00:00:46.960 --> 00:00:49.380 that we already know how to build, 24 00:00:49.380 --> 00:00:50.280 and but it's a bit long, 25 00:00:50.280 --> 00:00:52.030 so I'll quickly go through it. 26 00:00:52.030 --> 00:00:54.550 We're selecting the options table columns, 27 00:00:54.550 --> 00:00:58.030 and the vote count from the votes table 28 00:00:58.030 --> 00:01:00.300 and from the options table, joining on votes. 29 00:01:00.300 --> 00:01:02.733 And grouping by option ID. 30 00:01:04.250 --> 00:01:06.340 So that's gonna give us something like this, 31 00:01:06.340 --> 00:01:08.130 do take a moment if you need to pause it 32 00:01:08.130 --> 00:01:11.750 and understand sort of how we're arriving at this data. 33 00:01:11.750 --> 00:01:15.070 But this is essentially what we're gonna end up getting 34 00:01:15.070 --> 00:01:16.310 for each option, 35 00:01:16.310 --> 00:01:19.050 how many votes were cast, essentially. 36 00:01:19.050 --> 00:01:22.220 This doesn't quite give us what we wanted. 37 00:01:22.220 --> 00:01:25.200 So the next thing is we have to do some sorting. 38 00:01:25.200 --> 00:01:28.570 And we're gonna start off by sorting by vote count. 39 00:01:28.570 --> 00:01:30.530 So I'm adding an order by vote count 40 00:01:30.530 --> 00:01:31.990 in descending order there. 41 00:01:31.990 --> 00:01:34.170 And what that's gonna do is it's going to resort our data, 42 00:01:34.170 --> 00:01:38.500 give us the options with the highest vote count at the top. 43 00:01:38.500 --> 00:01:42.250 And this is gonna be a bit closer to what we want. 44 00:01:42.250 --> 00:01:43.890 But there is a small problem, 45 00:01:43.890 --> 00:01:47.960 which is if we have two options for the same poll 46 00:01:47.960 --> 00:01:51.050 and both of those options have, let's say 10 votes, 47 00:01:51.050 --> 00:01:52.520 they are gonna to the top 48 00:01:52.520 --> 00:01:54.640 even though they're in the same poll. 49 00:01:54.640 --> 00:01:57.560 Because the next most voted option would be down here 50 00:01:57.560 --> 00:01:59.270 and that would only have two votes. 51 00:01:59.270 --> 00:02:01.800 So we could potentially end up with many options 52 00:02:01.800 --> 00:02:03.600 with poll ID one at the top, 53 00:02:03.600 --> 00:02:05.710 and then some other options with a different poll ID 54 00:02:05.710 --> 00:02:06.820 at the bottom. 55 00:02:06.820 --> 00:02:11.100 So if we want to get the top option for each poll, 56 00:02:11.100 --> 00:02:13.150 then this query here 57 00:02:13.150 --> 00:02:14.830 isn't quite gonna give us what we want. 58 00:02:14.830 --> 00:02:17.210 So in order to help us achieve what we want, 59 00:02:17.210 --> 00:02:19.020 I'm gonna add another ORDER BY 60 00:02:19.020 --> 00:02:21.410 which is ORDER BY poll ID first. 61 00:02:21.410 --> 00:02:24.330 That way, what we're getting now 62 00:02:24.330 --> 00:02:26.590 is for each poll, 63 00:02:26.590 --> 00:02:29.960 the options sorted by vote count. 64 00:02:29.960 --> 00:02:31.820 Now this is a bit more useful 65 00:02:31.820 --> 00:02:35.050 because now what we can do is we can go and grab 66 00:02:35.050 --> 00:02:38.840 the first or the top item of each poll ID, 67 00:02:38.840 --> 00:02:40.470 we can say, Okay, 68 00:02:40.470 --> 00:02:43.180 now what I wanna do is I wanna grab this data set, 69 00:02:43.180 --> 00:02:45.130 and from each poll ID 70 00:02:45.130 --> 00:02:48.170 grab the top element only. 71 00:02:48.170 --> 00:02:51.490 And we can do that because we have sorted by poll ID. 72 00:02:51.490 --> 00:02:52.680 And the way we do that, 73 00:02:52.680 --> 00:02:56.150 is with the DISTINCT ON clause, 74 00:02:56.150 --> 00:03:00.300 for DISTINCT ON essentially groups by this column. 75 00:03:00.300 --> 00:03:01.840 Options dot poll ID, 76 00:03:01.840 --> 00:03:06.270 but only grabs the first row of each group. 77 00:03:06.270 --> 00:03:08.480 And so that's essentially what we're doing 78 00:03:08.480 --> 00:03:10.640 what we wanted to do with the previous table, 79 00:03:10.640 --> 00:03:12.860 we can do with DISTINCT ON. 80 00:03:12.860 --> 00:03:14.430 Something important is that 81 00:03:14.430 --> 00:03:16.880 you have to ORDER BY poll ID 82 00:03:16.880 --> 00:03:20.440 as the first thing your ordering by in this clause, 83 00:03:20.440 --> 00:03:22.340 if you want to use DISTINCT ON. 84 00:03:22.340 --> 00:03:24.660 So clearly, I've been planning this set of queries 85 00:03:24.660 --> 00:03:26.090 in order to lead us here. 86 00:03:26.090 --> 00:03:27.390 And but this is what you have to do 87 00:03:27.390 --> 00:03:28.930 in order to use DISTINCT ON. 88 00:03:28.930 --> 00:03:32.200 And what DISTINCT ON does is it grabs the first row 89 00:03:32.200 --> 00:03:33.420 of each group 90 00:03:33.420 --> 00:03:37.330 that would be determined by grouping on options dot poll ID. 91 00:03:37.330 --> 00:03:38.480 I know this is quite complicated, 92 00:03:38.480 --> 00:03:41.780 but do and pause the video, give it a try. 93 00:03:41.780 --> 00:03:44.260 Run the queries yourself. 94 00:03:44.260 --> 00:03:45.840 Look at the official documentation. 95 00:03:45.840 --> 00:03:47.160 You know, different explanations 96 00:03:47.160 --> 00:03:48.350 are always gonna help as well. 97 00:03:48.350 --> 00:03:51.880 And in the end, what we do end up with is the two options, 98 00:03:51.880 --> 00:03:54.040 one for each poll ID 99 00:03:54.040 --> 00:03:57.780 and they are the highest vote count options in each poll. 100 00:03:57.780 --> 00:03:59.980 There you go, the ORDER BY poll ID is necessary. 101 00:03:59.980 --> 00:04:02.760 At he top of the ORDER BY clause. 102 00:04:02.760 --> 00:04:07.600 So the DISTINCT ON clause or command 103 00:04:07.600 --> 00:04:10.890 allows us to keep the first row of the returned results 104 00:04:10.890 --> 00:04:13.490 after grouping and order. 105 00:04:13.490 --> 00:04:15.640 It's unique to PostgreSQL by the way, 106 00:04:15.640 --> 00:04:17.490 so don't go try this on MySQL. 107 00:04:17.490 --> 00:04:20.960 Clearly, when we vote on Postgres versus MySQL, 108 00:04:20.960 --> 00:04:22.750 Postgres must always come ahead. 109 00:04:22.750 --> 00:04:24.650 And this is unique to Postgres. 110 00:04:24.650 --> 00:04:28.750 DISTINCT without the ON is a different thing entirely 111 00:04:28.750 --> 00:04:31.300 DISTINCT is used immediately after the select statement, 112 00:04:31.300 --> 00:04:33.610 and it applies to all the columns been selected, 113 00:04:33.610 --> 00:04:35.780 and the database will only return those rows 114 00:04:35.780 --> 00:04:38.360 for which columns set has unique values. 115 00:04:38.360 --> 00:04:40.860 This is much simpler, so I'm gonna through this now, 116 00:04:40.860 --> 00:04:42.840 let's say that we've got some votes here, 117 00:04:42.840 --> 00:04:43.970 the same as before, 118 00:04:43.970 --> 00:04:46.130 and we do select DISTINCT username from votes, 119 00:04:46.130 --> 00:04:49.100 that's just gonna give us one unique usernames really. 120 00:04:49.100 --> 00:04:52.400 I said that you apply the DISTINCT keyword after select 121 00:04:52.400 --> 00:04:55.310 and it applies to all columns being selected. 122 00:04:55.310 --> 00:04:57.690 So let's see that we've got some cities like this, 123 00:04:57.690 --> 00:04:59.690 and we've got essentially four cities 124 00:04:59.690 --> 00:05:01.510 but the have the same name, 125 00:05:01.510 --> 00:05:03.620 two of them have the same names. 126 00:05:03.620 --> 00:05:05.630 And so you're gonna just select DISTINCT name 127 00:05:05.630 --> 00:05:08.910 from cities, that's going to give you the unique names, 128 00:05:08.910 --> 00:05:11.620 or rather, is gonna give you the unique values 129 00:05:11.620 --> 00:05:14.490 for the name column that you've selected. 130 00:05:14.490 --> 00:05:17.320 But if we do something like select distinct name 131 00:05:17.320 --> 00:05:18.950 comma my county. 132 00:05:18.950 --> 00:05:22.960 The DISTINCT clause applies to both name and county. 133 00:05:22.960 --> 00:05:24.600 So now what you're gonna get 134 00:05:24.600 --> 00:05:27.310 is Springfield, Essex one time, 135 00:05:27.310 --> 00:05:31.480 because Springfield, Essex is unique in its own group, 136 00:05:31.480 --> 00:05:33.990 and then Washington, Massachusetts and Washington, London. 137 00:05:33.990 --> 00:05:35.510 And so DISTINCT here 138 00:05:35.510 --> 00:05:39.020 is getting you that unique combination of name and county, 139 00:05:39.020 --> 00:05:41.660 so Springfield, Essex is a unique combination, 140 00:05:41.660 --> 00:05:43.390 Springfield, Essex here is not unique 141 00:05:43.390 --> 00:05:45.430 because it was already there at the top. 142 00:05:45.430 --> 00:05:47.870 But Washington, Massachusetts is unique as a combination. 143 00:05:47.870 --> 00:05:49.370 And so is Washington, London. 144 00:05:49.370 --> 00:05:52.000 I made up the cities by the way, so yeah. 145 00:05:52.000 --> 00:05:53.350 So a quick recap then 146 00:05:53.350 --> 00:05:56.080 DISTINCT ON applies on a single column 147 00:05:56.080 --> 00:05:57.040 or multiple columns, 148 00:05:57.040 --> 00:06:00.130 but you can pick which columns you want to apply it on. 149 00:06:00.130 --> 00:06:03.710 Essentially what it does is it mimics a GROUP BY 150 00:06:03.710 --> 00:06:06.930 and gives you just the first row of the group. 151 00:06:06.930 --> 00:06:09.930 DISTINCT though applies on all columns being selected 152 00:06:09.930 --> 00:06:13.050 and you can't pick which ones it does apply on. 153 00:06:13.050 --> 00:06:16.990 Finally DISTINCT ON requires an order by for consistency. 154 00:06:16.990 --> 00:06:18.420 If you don't have the ORDER BY 155 00:06:18.420 --> 00:06:20.060 then getting the first column 156 00:06:20.060 --> 00:06:21.760 of the group wouldn't really make sense 157 00:06:21.760 --> 00:06:24.020 because the order would essentially be random 158 00:06:24.020 --> 00:06:26.060 or not guaranteed. 159 00:06:26.060 --> 00:06:28.300 All right, hope this makes a little bit of sense. 160 00:06:28.300 --> 00:06:29.500 Thank you for joining me in this video. 161 00:06:29.500 --> 00:06:32.203 Thanks for watching, and I'll see you in the next one.