1
00:00:00,090 --> 00:00:03,010
Next up, we're going to learn how to sort our results.

2
00:00:03,030 --> 00:00:14,340
Right now, if I just do a simple select, how about just book ID, title and author L name from books?

3
00:00:14,850 --> 00:00:17,250
Our data is technically in an order.

4
00:00:17,250 --> 00:00:21,630
It is sorted based off of the order that our data is in the database.

5
00:00:21,630 --> 00:00:21,900
Right.

6
00:00:21,900 --> 00:00:28,320
If I insert something next, if I do an insert into books, I'm going to delete this row in just a moment.

7
00:00:28,320 --> 00:00:34,890
So I'm not going to put all the values in, but let's say I'm going to put title and author L Neiman.

8
00:00:36,770 --> 00:00:45,560
Author l name values and I'll just call the title My Life and it's written by me.

9
00:00:45,980 --> 00:00:47,360
Last name Steel.

10
00:00:47,930 --> 00:00:48,470
It doesn't matter.

11
00:00:48,470 --> 00:00:52,430
But my point that I want to make is that that data is inserted, it's in there.

12
00:00:52,430 --> 00:00:58,040
And when I select back out, I select all my my rows without any sort of sorting.

13
00:00:58,040 --> 00:01:01,790
That newest result is going to be at the end, by the way.

14
00:01:01,850 --> 00:01:04,220
You might be wondering why did it jump to 22?

15
00:01:04,580 --> 00:01:06,110
I recorded this video once before.

16
00:01:06,110 --> 00:01:10,040
I wasn't happy with it, so I deleted some rows 20 and 21.

17
00:01:10,610 --> 00:01:16,130
And so those ideas were basically taken up and the auto increment just keeps moving on.

18
00:01:16,130 --> 00:01:22,640
So it's a relic from a bad recording I made that I just didn't really I didn't like how the video unfolded,

19
00:01:22,640 --> 00:01:24,140
so I redid it anyway.

20
00:01:24,620 --> 00:01:25,940
You'll see that it's at the end here.

21
00:01:25,940 --> 00:01:26,840
So there is an order.

22
00:01:26,840 --> 00:01:30,920
It's based off of that insertion order, but that's probably not what we want.

23
00:01:30,920 --> 00:01:37,640
So there is an order here, but often we want to sort in some other way, sort by the titles alphabetically

24
00:01:37,640 --> 00:01:43,670
or by the last name of authors or by the number of the release here or the stock that we have.

25
00:01:43,670 --> 00:01:51,140
The quantity of each book in the way that we do that is by using order by and it's order space by two

26
00:01:51,140 --> 00:01:55,400
different words, and it comes after our select.

27
00:01:55,400 --> 00:01:57,680
So we have whatever we're trying to select.

28
00:01:57,680 --> 00:02:04,520
And then at the end we can say, by the way, here's how I want you to order it by order by and then

29
00:02:04,520 --> 00:02:05,690
some column name.

30
00:02:05,690 --> 00:02:07,580
So author L name.

31
00:02:07,790 --> 00:02:09,050
Let's see if it works.

32
00:02:09,259 --> 00:02:11,450
So here's what I had before.

33
00:02:11,450 --> 00:02:14,150
Book ID, title and author, last name.

34
00:02:14,750 --> 00:02:19,580
Why don't we actually just get rid of the title just to make this a little easier to look at and instead

35
00:02:19,580 --> 00:02:22,040
do author f name too.

36
00:02:22,760 --> 00:02:26,600
So book ID authors, first name, author's last name.

37
00:02:26,600 --> 00:02:29,330
And I'm going to do an order by.

38
00:02:30,920 --> 00:02:34,580
Author ll name just as my first example.

39
00:02:35,120 --> 00:02:40,220
And we see the order completely changes and we get these alphabetized last names.

40
00:02:40,220 --> 00:02:44,570
So Carver is our first last name and then all the way down to Steinbeck.

41
00:02:45,480 --> 00:02:48,240
If I instead said author first name.

42
00:02:49,190 --> 00:02:50,140
We now see.

43
00:02:50,150 --> 00:02:51,830
Oh, there's no.

44
00:02:51,860 --> 00:02:56,120
I've got a null in there because I didn't put their first name in when I created that book.

45
00:02:56,120 --> 00:02:59,450
So Noelle comes first before any of my other alphabetical values.

46
00:02:59,450 --> 00:03:03,140
And then we have Dan Dave all the way down to Raymond.

47
00:03:03,410 --> 00:03:06,350
So that's our first example of using order by.

48
00:03:06,620 --> 00:03:08,120
Just remember, it comes at the end.

49
00:03:08,120 --> 00:03:14,900
I've seen people make that mistake and try and do something like select book ID, author, F name,

50
00:03:14,900 --> 00:03:20,360
author, L name, and then put order by here from books and it just doesn't work.

51
00:03:20,360 --> 00:03:22,310
It's invalid SQL syntax.

52
00:03:22,550 --> 00:03:24,650
Now there's a bit more to know about order by.

53
00:03:24,680 --> 00:03:29,210
The first thing is that it ascends or it sorts in ascending order by default.

54
00:03:29,210 --> 00:03:32,900
So alphabetically that means going from A to Z.

55
00:03:33,140 --> 00:03:41,630
But we can change that by saying we want descending order DSC, which somewhat confusingly we also use

56
00:03:41,630 --> 00:03:48,680
DSC to mean describe right when we describe a table, it's short for describe books.

57
00:03:49,400 --> 00:03:56,780
But when we use it after order by, let's do the same query again, Order by author first name, right.

58
00:03:56,780 --> 00:03:58,010
We're going from A to Z.

59
00:03:58,040 --> 00:04:00,950
Of course we don't have an A, so it's D down to R.

60
00:04:01,070 --> 00:04:09,160
But if I then say DSC and that has to go here right after the order by, put my semicolon in.

61
00:04:09,170 --> 00:04:17,390
Now we go from R at the top down to Dan and then null, which is considered the lowest value alphabetically.

62
00:04:18,110 --> 00:04:20,240
So you can change that disc.

63
00:04:20,269 --> 00:04:24,410
I think you can even say a SC if you wanted to.

64
00:04:24,440 --> 00:04:27,950
For ascending, which is again the default.

65
00:04:27,950 --> 00:04:32,780
So we're going from NULL then dan A down to Z for Raymond.

66
00:04:32,780 --> 00:04:36,650
Ah, we don't have a Z and we can also do numbers.

67
00:04:36,650 --> 00:04:40,220
Of course, this is probably more common than alphabetizing things.

68
00:04:41,000 --> 00:04:44,330
Let's see, we've got let's look at our books again.

69
00:04:45,050 --> 00:04:49,790
We've got how about release gear or stock quantity or pages.

70
00:04:49,790 --> 00:04:51,650
Let's sort by pages to start.

71
00:04:51,650 --> 00:04:56,810
So let's select title and pages from books.

72
00:04:59,400 --> 00:04:59,790
Okay.

73
00:04:59,910 --> 00:05:02,970
And I want to sort by pages.

74
00:05:02,970 --> 00:05:06,240
So I'll do an order by pages.

75
00:05:06,570 --> 00:05:10,520
And then do I want the default order of ascending or do I want descending?

76
00:05:10,530 --> 00:05:15,900
Or if I do ascending the default, we start with null because of that one bad book I inserted, which

77
00:05:15,900 --> 00:05:16,290
is fine.

78
00:05:16,290 --> 00:05:18,120
I didn't specify Page's value.

79
00:05:18,390 --> 00:05:22,860
And then we have 176 pages up to 634 pages.

80
00:05:22,860 --> 00:05:26,400
And then of course we could change that to be ascending instead.

81
00:05:27,150 --> 00:05:30,840
Additionally, we could sort by release here.

82
00:05:31,020 --> 00:05:34,580
What about if I tried this released year?

83
00:05:34,710 --> 00:05:39,180
What happens here if I'm not even selecting released year?

84
00:05:39,210 --> 00:05:40,320
It's there.

85
00:05:40,320 --> 00:05:42,210
I'm pretty sure it's called released here.

86
00:05:42,210 --> 00:05:43,320
We could always verify.

87
00:05:43,320 --> 00:05:44,490
Yeah, released.

88
00:05:44,490 --> 00:05:48,120
Underscore year it's numeric, but I'm not selecting it.

89
00:05:48,120 --> 00:05:50,330
I'm only selecting title and pages.

90
00:05:50,340 --> 00:05:51,330
What happens?

91
00:05:51,690 --> 00:05:58,470
Well, it actually still works If you take a look here, we have Cannery Row all the way up to Lincoln

92
00:05:58,470 --> 00:05:59,490
in the Bardo.

93
00:05:59,490 --> 00:06:03,660
Now, if I put release year in here so that we can actually see it.

94
00:06:07,880 --> 00:06:09,550
You'll see the order doesn't change.

95
00:06:09,560 --> 00:06:11,540
We go from Cannery Row again.

96
00:06:11,540 --> 00:06:16,780
I'm ignoring the null Cannery Row up to 2017 for Lincoln in the Bardo.

97
00:06:16,790 --> 00:06:23,150
So that's probably not that common to sort by or order by some column that you're not even selecting

98
00:06:23,150 --> 00:06:28,640
because you won't be able to see the values that you're sorting by, but you can do it.

99
00:06:28,640 --> 00:06:33,260
So we have a bit more to talk about with Order by, but I'm going to split it into two videos to try

100
00:06:33,260 --> 00:06:35,630
and, I don't know, preserve your sanity.

101
00:06:35,630 --> 00:06:37,250
I guess I'll see you in the next one.