1 00:00:00,180 --> 00:00:06,300 Another thing you may come across with order by looks like this ordered by and then a number. 2 00:00:06,330 --> 00:00:08,070 What on earth does that mean? 3 00:00:08,100 --> 00:00:13,050 It's basically a shorthand syntax that says ordered by the second column that we're selecting. 4 00:00:13,060 --> 00:00:18,420 So if we're selecting title author, F name and author L name order by that second thing, I guess it 5 00:00:18,420 --> 00:00:21,870 saves you a little bit of typing so you don't have to type author f name. 6 00:00:21,880 --> 00:00:23,400 Let me show an example of that. 7 00:00:23,400 --> 00:00:33,960 So let's do select title is kind of long, but let's do book ID and then author F name, author L name 8 00:00:33,960 --> 00:00:38,490 and let's do pages from books. 9 00:00:39,270 --> 00:00:42,540 Okay, So I want to sort by pages. 10 00:00:42,540 --> 00:00:46,620 One option, of course, is just to say order by pages. 11 00:00:46,620 --> 00:00:48,150 I mean, that's not that much work. 12 00:00:48,660 --> 00:00:50,520 And what did I mess up here? 13 00:00:51,300 --> 00:00:56,700 Oh, I somehow got rid of the table name from books ordered by pages, and that works fine. 14 00:00:56,910 --> 00:01:02,700 But the syntax I'm showing you here is we can replace pages with a number. 15 00:01:02,730 --> 00:01:05,040 This would be the first column, second, third. 16 00:01:05,040 --> 00:01:06,300 And this is the fourth column. 17 00:01:06,300 --> 00:01:13,710 I'll sort by or order by four and we get the same result null 176 up to 634. 18 00:01:13,710 --> 00:01:16,980 And then, of course, I could say descending and go the other direction. 19 00:01:16,980 --> 00:01:21,390 If I want to sort by author first name, I could say order by two. 20 00:01:21,390 --> 00:01:23,760 I'm honestly not really a fan of this. 21 00:01:23,880 --> 00:01:26,130 It's just not as clear. 22 00:01:26,130 --> 00:01:28,680 If you look at this, you then have to go reference over here. 23 00:01:28,680 --> 00:01:35,130 So maybe it saves you a couple of characters of typing, but it also clutters your or it makes your 24 00:01:35,130 --> 00:01:41,010 code less meaningful, less obvious if someone else is looking at it or just future you is looking at 25 00:01:41,010 --> 00:01:41,250 it. 26 00:01:41,250 --> 00:01:45,720 But as you can see here, when I do two, we're sorting by author F name. 27 00:01:45,720 --> 00:01:48,240 So the first name in descending order. 28 00:01:48,240 --> 00:01:55,020 So from Z to A, in our case Raymond to Dan and NULL, the next thing I'll show you with order by is 29 00:01:55,020 --> 00:01:57,480 that we can actually order by multiple columns. 30 00:01:57,480 --> 00:02:04,860 So when we do this, we'll sort by one column first and then secondly, we'll sort by the second column 31 00:02:04,860 --> 00:02:06,270 or third or fourth and fifth. 32 00:02:06,270 --> 00:02:12,660 So it's not saying sort them together, it's sorting by this and then this column. 33 00:02:12,780 --> 00:02:15,750 So I'll show you an example where this might make sense. 34 00:02:15,750 --> 00:02:25,080 Let's do a select author l name and then about released year and then finally title from books. 35 00:02:25,950 --> 00:02:26,880 So here's our result. 36 00:02:26,880 --> 00:02:33,570 Nothing is ordered by yet, but now let's say I want to order by author last name. 37 00:02:33,570 --> 00:02:36,360 I want to see them sorted by the author's last name. 38 00:02:36,360 --> 00:02:42,510 And so now we see all the Carver books together, all the Eggers books together, Foster Wallace, Gaiman. 39 00:02:42,510 --> 00:02:47,340 But then within each group, they're right where the author last name is the same. 40 00:02:47,610 --> 00:02:49,950 There could be an additional sort. 41 00:02:49,950 --> 00:02:55,590 Maybe I want them also to be sorted by the release year, because right now if we look at Eggers, we 42 00:02:55,590 --> 00:02:58,470 have 2012, 2013 and then 2001. 43 00:02:58,560 --> 00:03:02,970 Or if you look at Gaiman, we have 2016, 2001, 2003. 44 00:03:03,390 --> 00:03:10,920 So secondarily, I could add on after author l name in my order by right here, I'll say then order 45 00:03:10,920 --> 00:03:12,540 by released year. 46 00:03:13,740 --> 00:03:19,650 So this now will look very similar, at least on the left hand side where we see the last names. 47 00:03:19,650 --> 00:03:24,600 But what has changed is the order of the rows within each one of these chunks. 48 00:03:24,600 --> 00:03:30,540 So within the Eggers grouping we now have 2001 2012, 2013. 49 00:03:30,540 --> 00:03:32,610 Again, compare that to what we had before. 50 00:03:32,610 --> 00:03:37,650 We still had the three eggers rows together, but their order within that grouping was different. 51 00:03:37,650 --> 00:03:42,060 It was 2012 or yeah, 12, 13 and one, right? 52 00:03:42,060 --> 00:03:45,240 And now it is 112 and 13. 53 00:03:45,240 --> 00:03:54,480 And then same thing with Neil Gaiman, 2001 2003 2016 and we could sort by a third value like by title. 54 00:03:54,480 --> 00:04:00,960 It's just that we don't really have any con or any duplicate values for release here. 55 00:04:00,960 --> 00:04:05,040 So there's no real way for me to show you that it works, but you could keep going. 56 00:04:05,610 --> 00:04:07,410 And we can also change direction. 57 00:04:07,410 --> 00:04:10,740 So I could say I want to actually sort released here. 58 00:04:11,460 --> 00:04:12,540 Descending. 59 00:04:12,570 --> 00:04:19,500 So DSC so we're still sorting ascending for author last name that happens first all the rows are going 60 00:04:19,500 --> 00:04:22,730 to be ordered by author last name alphabetically A to Z. 61 00:04:22,740 --> 00:04:27,000 And then after that we will sort by release gear and this is descending. 62 00:04:27,000 --> 00:04:35,580 So 2013 most recent, then 12 and then 2001 for Dave Eggers or for Carver, we have 89 and then 81. 63 00:04:35,880 --> 00:04:41,730 So that's how you can sort by multiple values, one column and then another column afterwards. 64 00:04:42,090 --> 00:04:47,940 And then one last thing that I should show you about order by is that we can also order by columns that 65 00:04:47,940 --> 00:04:53,790 aren't necessarily part of the table, but our results are values that we've asked SQL for. 66 00:04:53,790 --> 00:04:59,610 For example, remember that we can do things like can cat select can cat? 67 00:05:01,340 --> 00:05:09,080 And then let's take author, f name, comma, author, LL name, and then I guess I'll put a space in 68 00:05:09,080 --> 00:05:09,650 there. 69 00:05:10,870 --> 00:05:13,000 And let's call that as. 70 00:05:13,630 --> 00:05:14,590 Full name. 71 00:05:15,690 --> 00:05:20,670 Or just how about just author as author from books. 72 00:05:21,630 --> 00:05:26,250 So we're we're not sorting anything, but we're just getting these full names using Kit-Kat. 73 00:05:26,490 --> 00:05:33,770 I can then order by author if that's the name that I gave my column there that I retrieved. 74 00:05:33,780 --> 00:05:34,200 Right. 75 00:05:34,200 --> 00:05:35,970 This is not part of the database. 76 00:05:35,970 --> 00:05:37,560 It's not a part of the table. 77 00:05:37,560 --> 00:05:44,880 There's no column called Author, but I have Alias this information called it author using the as word, 78 00:05:44,910 --> 00:05:47,100 the as word using as. 79 00:05:47,460 --> 00:05:50,430 And then I can order by author, as you can see. 80 00:05:50,430 --> 00:05:53,520 There we go, Dan Harris, all the way down to Raymond Carver. 81 00:05:53,520 --> 00:05:55,260 And I think that's good enough. 82 00:05:55,260 --> 00:05:58,650 I hope you get the picture there by using an alias. 83 00:05:58,650 --> 00:06:02,550 I can then reference that name and order by later on. 84 00:06:03,030 --> 00:06:04,260 So there's a lot to order by. 85 00:06:04,260 --> 00:06:10,500 But at the end of the day, usually I use it just to order by alphabetically, but more commonly numerically. 86 00:06:10,500 --> 00:06:14,100 And occasionally I want something to be descending instead of the default ascending. 87 00:06:14,100 --> 00:06:19,620 And then somewhat rarer you can order by multiple columns one after another, as we saw here.