1 00:00:00,090 --> 00:00:02,280 Moving on along to our next topic. 2 00:00:02,760 --> 00:00:03,710 I guess that's okay. 3 00:00:03,719 --> 00:00:04,830 Moving on along. 4 00:00:05,400 --> 00:00:06,480 I'll keep using that. 5 00:00:06,900 --> 00:00:09,180 Our next topic here is Order By. 6 00:00:09,540 --> 00:00:14,850 So as a subtitle tells you, it's how we can sort our results, which is something that we'll want to 7 00:00:14,850 --> 00:00:15,180 do. 8 00:00:15,210 --> 00:00:18,390 Hopefully you see why that's useful when you're trying to understand things. 9 00:00:18,690 --> 00:00:20,560 It's good to be able to sort your data. 10 00:00:20,580 --> 00:00:24,840 So with our books, for instance, right now we're getting them all in the order that they were inserted 11 00:00:24,840 --> 00:00:25,230 in. 12 00:00:25,500 --> 00:00:31,710 And maybe we want to do things like find the best selling books or the worst selling books or find the 13 00:00:31,710 --> 00:00:35,300 most active users on our website or the least active users or something similar. 14 00:00:35,310 --> 00:00:40,290 Find the most upvoted posts or the post that has the most comments on our blog or whatever we're working 15 00:00:40,290 --> 00:00:40,630 with. 16 00:00:40,650 --> 00:00:42,620 It's important to be able to sort things. 17 00:00:42,630 --> 00:00:46,020 And again, the way we do that is by using order by. 18 00:00:46,350 --> 00:00:48,090 So let's take a look at an example. 19 00:00:48,750 --> 00:00:53,880 Here's a query that's going to select all the author last names from books. 20 00:00:53,880 --> 00:00:56,620 So up until this point, nothing is new. 21 00:00:56,640 --> 00:00:59,160 Select author last name from book or books. 22 00:00:59,640 --> 00:01:04,230 But after that we add on this little thing order space by. 23 00:01:04,260 --> 00:01:06,900 That's something that trips me up, by the way, that there is a space. 24 00:01:07,290 --> 00:01:10,350 Sometimes it feels like it should be one word or there should be an underscore. 25 00:01:10,350 --> 00:01:13,110 But there's a space or space by. 26 00:01:13,590 --> 00:01:15,390 And then what do we want to order it by? 27 00:01:15,660 --> 00:01:16,950 By the last name. 28 00:01:17,460 --> 00:01:18,450 So let me show you. 29 00:01:18,870 --> 00:01:23,670 If we go here, we just do a select author l name from books. 30 00:01:24,540 --> 00:01:26,400 This should be review what we get. 31 00:01:27,390 --> 00:01:30,360 We get them in the order they were inserted. 32 00:01:30,360 --> 00:01:32,850 So it's not alphabetical at all. 33 00:01:33,750 --> 00:01:42,840 So if we want to do alphabetical, select author l name from books order by and then we have to say 34 00:01:42,840 --> 00:01:44,970 author l name just like that. 35 00:01:46,520 --> 00:01:53,000 And as you can see now, we have Carver up top all the way down to Steinbeck, and you should notice 36 00:01:53,000 --> 00:01:56,930 it is sorted by default in ascending order. 37 00:01:57,290 --> 00:02:04,250 So from C or A technically down to Z and it's actually alphanumeric as well. 38 00:02:04,250 --> 00:02:10,340 So if we do titles, for instance, let's just do select title from books. 39 00:02:12,200 --> 00:02:16,850 We've got a title that has an A at the beginning, but we also have 10% happier. 40 00:02:17,420 --> 00:02:29,810 So if we do this select title from Books Order by title, you'll see we get the 10% happier first before 41 00:02:29,810 --> 00:02:32,150 the A's and all the way down to W. 42 00:02:32,300 --> 00:02:35,240 So as I mentioned, it is ascending by default. 43 00:02:35,240 --> 00:02:39,110 So if you don't specify an order, it just assumes you want ascending. 44 00:02:39,410 --> 00:02:46,850 But of course, we can change that, as my slide says, by just providing one thing at the end. 45 00:02:47,330 --> 00:02:52,820 So everything up until this point is the same select author l name from books order by author l name 46 00:02:52,820 --> 00:02:55,820 space DSC. 47 00:02:56,060 --> 00:03:00,980 And in my opinion, this is also something that's kind of confusing because it's DSC. 48 00:03:00,980 --> 00:03:05,900 But then the abbreviation for Ascending is AC, which is only three letters versus four. 49 00:03:05,990 --> 00:03:07,880 But that's just my opinion. 50 00:03:07,880 --> 00:03:08,720 So let's try it. 51 00:03:08,720 --> 00:03:14,810 Now, if we recall what we had select author l name from books order by author L name. 52 00:03:15,200 --> 00:03:15,910 There we go. 53 00:03:15,920 --> 00:03:19,310 This is the original in default ascending order. 54 00:03:19,310 --> 00:03:23,510 But if we add DSC, you can see it's reversed. 55 00:03:23,510 --> 00:03:26,180 Now it goes from Steinbeck down to Carver. 56 00:03:26,450 --> 00:03:35,810 Or if we do the same thing with our title and we just add on DSC, it now goes from white noise down 57 00:03:35,810 --> 00:03:37,010 to 10% happier. 58 00:03:37,640 --> 00:03:40,070 And of course order by will also work with numbers. 59 00:03:40,070 --> 00:03:46,220 So it's pretty common that we want to sort things numerically, whether it's the number of orders or 60 00:03:46,220 --> 00:03:52,790 the sort, the highest priced items or like I said earlier, sort the blog posts by whichever have the 61 00:03:52,790 --> 00:03:55,550 most comments, most to least or something like that. 62 00:03:55,550 --> 00:03:56,930 Those are numeric values. 63 00:03:57,260 --> 00:04:00,260 So to do that, it's the exact same syntax. 64 00:04:00,260 --> 00:04:02,810 All that we change is what we're ordering by. 65 00:04:03,020 --> 00:04:05,900 So in this case released here, which is. 66 00:04:07,700 --> 00:04:13,880 So in this case released here, which is an integer select release here from books order by release 67 00:04:13,880 --> 00:04:14,290 here. 68 00:04:14,300 --> 00:04:15,470 So let's try it out. 69 00:04:16,430 --> 00:04:24,200 Let's do a select release here from books without the order by and there in all sorts of crazy order. 70 00:04:24,230 --> 00:04:31,160 I think our lowest one is 1945 all the way up to 2017, which is that Lincoln in the Bottle Book definitely 71 00:04:31,160 --> 00:04:32,090 recommend it still. 72 00:04:32,570 --> 00:04:39,800 So if we recall that line and just add on order by release dear. 73 00:04:42,680 --> 00:04:43,300 There we go. 74 00:04:43,310 --> 00:04:48,680 And by default, it's also ascending 1945 up to 2017. 75 00:04:49,040 --> 00:04:51,800 We could reverse it with DSC. 76 00:04:52,370 --> 00:04:53,240 Just like that. 77 00:04:53,960 --> 00:05:01,850 And just to prove to you you can do this, you can be explicit with ASC ascending and get the same order 78 00:05:02,090 --> 00:05:03,590 if you left it off. 79 00:05:04,520 --> 00:05:06,710 And not to harp on this too much, but I just. 80 00:05:06,710 --> 00:05:09,200 I don't like that this is four letters versus three letters. 81 00:05:09,200 --> 00:05:11,000 It's just it should be consistent. 82 00:05:11,420 --> 00:05:13,880 So if we leave it off, though, we get the same results. 83 00:05:15,110 --> 00:05:16,760 So that's the basics of order by. 84 00:05:17,450 --> 00:05:23,120 Another thing I'd like to highlight here is that we've been doing things like select release year order 85 00:05:23,120 --> 00:05:28,610 by release here where it's matched, what we're selecting is matching how we're ordering it. 86 00:05:28,820 --> 00:05:29,930 You don't have to do that. 87 00:05:29,930 --> 00:05:42,230 So let's say we wanted to get let's say we want title and year released here, that is and pages or 88 00:05:42,230 --> 00:05:42,770 not. 89 00:05:43,100 --> 00:05:54,770 And I want all three of those from books order by and we'll do release here so I can do that and it's 90 00:05:54,770 --> 00:05:58,400 going to order them by the release here but still gives me all the data. 91 00:05:58,610 --> 00:06:03,500 So it's not like it has to match or it could even leave released. 92 00:06:03,500 --> 00:06:04,940 You're out of this entirely. 93 00:06:04,940 --> 00:06:12,080 I don't have to select it, but I can still order by so I could select title and pages in order by the 94 00:06:12,080 --> 00:06:12,940 release here. 95 00:06:13,280 --> 00:06:15,770 And you can see the first one we get is Cannery Row. 96 00:06:16,550 --> 00:06:22,420 And the first one we got here is Cannery Row from 1945 and the last one was Lincoln in the Prado 2017. 97 00:06:22,820 --> 00:06:28,040 Last one is Lincoln in the Bardo 2017, even though we don't have the year actually printing out. 98 00:06:28,550 --> 00:06:32,810 So that's all I want to show there, that you don't have to be ordering by something that you're selecting 99 00:06:32,810 --> 00:06:35,390 or that it doesn't have to be the only thing that you're selecting. 100 00:06:35,390 --> 00:06:41,480 So here's another little quirk of order by if you take a look at this query, select title, author 101 00:06:41,480 --> 00:06:43,190 f name, author l name. 102 00:06:43,190 --> 00:06:47,960 And I put this on two line tier, not because it's correct to do that, but just so that it's easier 103 00:06:47,960 --> 00:06:48,860 to read for you. 104 00:06:48,860 --> 00:06:53,480 So select title author f name and author l name from Books Order by two. 105 00:06:54,440 --> 00:06:55,970 What do you think the two refers to? 106 00:06:56,740 --> 00:07:00,190 Well, in this case, it's actually just a shortcut. 107 00:07:00,190 --> 00:07:02,650 So we don't have to rewrite author f name. 108 00:07:03,100 --> 00:07:08,110 So if we're selecting title author F name and author L name, this will be one. 109 00:07:08,110 --> 00:07:09,580 This is two and this is three. 110 00:07:09,940 --> 00:07:14,650 And it's a shortcut for me to say ordered by two author f name. 111 00:07:14,980 --> 00:07:16,450 So let's copy it over. 112 00:07:17,350 --> 00:07:21,460 Try running it and you can see the results we get back. 113 00:07:21,820 --> 00:07:26,980 They're not sorted alphabetically here, even though sort of looks like it because we have 10% happier 114 00:07:26,980 --> 00:07:32,530 and then a hologram for the King ten and then a but it's not the rest of the way is not. 115 00:07:32,800 --> 00:07:40,300 But you can see over here we've got Dan first and then David, Dave, Data, Don, Frieda, blah, blah, 116 00:07:40,300 --> 00:07:41,710 blah, down to Raymond. 117 00:07:42,460 --> 00:07:49,780 And if we wanted to sort instead by last name, we just changed it to three again because author l name 118 00:07:49,780 --> 00:07:51,910 is the third thing that we're selecting. 119 00:07:52,420 --> 00:07:59,920 And now we've go from Carver down to Steinbeck and likewise you can change it to one to sort by title, 120 00:08:00,370 --> 00:08:03,040 and we could also switch it over to descending. 121 00:08:03,160 --> 00:08:04,180 And that works. 122 00:08:04,930 --> 00:08:09,700 And if we did switch the order here just to show the order does matter. 123 00:08:09,700 --> 00:08:23,320 So if I do author l name comma title if I want to sort by title, I would need to do two now and now 124 00:08:23,320 --> 00:08:24,460 they're sorted by title. 125 00:08:26,080 --> 00:08:32,350 So there's one last thing and I made a slide to remind myself about it, which is this here? 126 00:08:32,770 --> 00:08:39,130 So what we can actually do is sort by or order by two different columns, which seems like something 127 00:08:39,130 --> 00:08:40,570 that you wouldn't really do that often. 128 00:08:40,570 --> 00:08:42,340 And truth be told, it's not. 129 00:08:42,340 --> 00:08:44,740 But I just want you to know that you can. 130 00:08:44,980 --> 00:08:51,970 So to show you what I'm talking about, let me first do a select and I'll make a new file for this one 131 00:08:51,970 --> 00:08:52,540 as well. 132 00:08:52,930 --> 00:08:56,860 Just because we're going to have a little bit of code, let's do new file. 133 00:08:56,920 --> 00:09:01,720 I'll just call it order underscore by SQL. 134 00:09:04,880 --> 00:09:16,580 And if we do a select author f name, comma, author, l name from books just like that, save it and 135 00:09:16,580 --> 00:09:20,750 we need to do our source order by SQL. 136 00:09:21,260 --> 00:09:22,130 Just like that. 137 00:09:22,580 --> 00:09:28,880 And I forgot the directory refining selections slash. 138 00:09:30,200 --> 00:09:33,210 So when I do that, we get first and last names are not sorted. 139 00:09:33,230 --> 00:09:37,280 So now if I do an order by author last name. 140 00:09:42,740 --> 00:09:43,820 And I redo it. 141 00:09:45,620 --> 00:09:46,650 Everything looks good. 142 00:09:46,670 --> 00:09:48,620 We have our authors sorted alphabetically. 143 00:09:48,620 --> 00:09:51,580 Carver, Gaiman, HARRIS. 144 00:09:51,590 --> 00:09:53,540 But then that's where we run into our problem, which is. 145 00:09:53,540 --> 00:09:55,850 HARRIS And it's not really a problem. 146 00:09:55,850 --> 00:10:01,370 But if we wanted our authors sorted alphabetically, technically, when there's two authors that have 147 00:10:01,370 --> 00:10:08,030 the same or the same last name and we want the entire author name to be sorted alphabetically, well 148 00:10:08,030 --> 00:10:09,720 then we need to go to the first name. 149 00:10:09,740 --> 00:10:15,590 And in this case, Frieda Harris is in front of Dan Harris, even though DD comes before F. 150 00:10:16,640 --> 00:10:18,710 So we want Dan to come before Frieda. 151 00:10:19,160 --> 00:10:21,650 And that's where this comes in handy. 152 00:10:21,650 --> 00:10:26,360 We can sort after we sort by last name, then sort by first name. 153 00:10:26,360 --> 00:10:28,130 So it's not going to redo everything. 154 00:10:28,160 --> 00:10:35,570 It's going to keep basically the initial sort and then sort first name if there's any conflicts. 155 00:10:36,020 --> 00:10:42,890 So we can just add that in as a comma, author, f name and save. 156 00:10:43,190 --> 00:10:47,270 Now if you just focus here, so we have Frieda Harris and then Dan Harris. 157 00:10:48,110 --> 00:10:52,400 And if I source that and run it, you can see it just switched. 158 00:10:52,400 --> 00:10:53,990 So everything else is the same. 159 00:10:54,170 --> 00:10:59,540 Carver first, Steinbeck last, but now Dan Harris comes before Frieda Harris. 160 00:11:00,290 --> 00:11:00,710 All right. 161 00:11:00,710 --> 00:11:05,190 So that's actually it to order by saw a lot there is a little bit dense. 162 00:11:05,190 --> 00:11:10,580 I apologize but order by is useful like I said you'll be sorting lots of things and it helps you understand 163 00:11:10,580 --> 00:11:15,740 your data, especially when we're talking about sales data or site specific data. 164 00:11:15,740 --> 00:11:19,040 There's a lot of insights that you can get just by putting it in an order.