1
00:00:00,120 --> 00:00:02,670
Next up will see a pair of built in functions.

2
00:00:02,670 --> 00:00:05,400
They go together, Min and Max.

3
00:00:05,520 --> 00:00:08,970
They're sort of relatively self-explanatory.

4
00:00:08,970 --> 00:00:15,990
Min helps us find the minimum value in a certain column, but also as we'll see shortly, within a certain

5
00:00:15,990 --> 00:00:16,710
group.

6
00:00:16,860 --> 00:00:18,600
And then Max is the opposite.

7
00:00:18,730 --> 00:00:20,340
It's going to find the maximum values.

8
00:00:20,610 --> 00:00:26,190
So we're going to start by just taking a look at how these work without grouped by in the same way that

9
00:00:26,190 --> 00:00:26,980
we saw.

10
00:00:27,390 --> 00:00:34,680
We can just do select count without any grouping from books, for example, or count the distinct authors

11
00:00:34,680 --> 00:00:35,210
or something.

12
00:00:35,220 --> 00:00:38,800
There's no group buy, but then we could also use count with a group.

13
00:00:38,820 --> 00:00:42,390
We're going to start by doing Min and Max without any grouping.

14
00:00:42,810 --> 00:00:45,990
So the first thing we'll look at is how we could do something like this.

15
00:00:45,990 --> 00:00:52,770
Find the minimum release here, aka the earliest year a book was published in our table.

16
00:00:53,490 --> 00:00:58,740
So we can just do select min released year.

17
00:01:00,730 --> 00:01:02,410
From books.

18
00:01:03,140 --> 00:01:05,690
And there we are, 1945.

19
00:01:05,720 --> 00:01:07,370
I believe that's a Steinbeck.

20
00:01:07,640 --> 00:01:12,830
But in a little bit we're going to learn how we could figure out exactly what row that belongs to.

21
00:01:12,950 --> 00:01:20,420
We could also do things like find the longest book well, at least find the most number of pages, select

22
00:01:20,420 --> 00:01:24,320
max of pages from books.

23
00:01:24,800 --> 00:01:27,980
So there's some book that has 634 pages.

24
00:01:27,980 --> 00:01:32,060
But again, we don't know exactly which road that belongs to.

25
00:01:32,090 --> 00:01:33,680
What's the title of that book?

26
00:01:33,680 --> 00:01:37,130
We'll get there, but this works to find the max.

27
00:01:37,190 --> 00:01:38,060
That's what I need.

28
00:01:39,410 --> 00:01:40,670
What about with text?

29
00:01:40,670 --> 00:01:42,410
We can also do select.

30
00:01:42,410 --> 00:01:49,010
Let's do min author l name and I can do multiple of these together as well.

31
00:01:49,010 --> 00:01:50,360
Show that in just a moment.

32
00:01:50,360 --> 00:01:51,560
Select min.

33
00:01:52,400 --> 00:01:54,710
Let's do from books.

34
00:01:54,740 --> 00:01:59,360
Obviously that's our only table right now and it does work alphabetically.

35
00:01:59,360 --> 00:02:02,600
It will find the lowest in the alphabet.

36
00:02:03,290 --> 00:02:11,420
Author last name, which is Carver and I could add on Max author l name if I wanted to.

37
00:02:11,720 --> 00:02:16,580
And now we're finding the lowest or the earliest alphabetically and the latest alphabetically.

38
00:02:16,580 --> 00:02:20,810
Author last name in our table, Carver and Steinbeck.

39
00:02:21,260 --> 00:02:22,460
So you can use it on text.

40
00:02:22,460 --> 00:02:23,990
I don't really do that often though.

41
00:02:24,020 --> 00:02:27,230
Usually I use Min and max for numerical values.

42
00:02:27,230 --> 00:02:33,980
And that now brings us to the question of what if I want to know the title of the longest book?

43
00:02:33,980 --> 00:02:36,350
We know how to find the longest book.

44
00:02:36,350 --> 00:02:37,790
We did it well.

45
00:02:37,790 --> 00:02:44,600
The number of pages select maximum pages from the entire table of books, and it just gives me the number.

46
00:02:45,050 --> 00:02:47,180
How do I know which book that belongs to?

47
00:02:47,720 --> 00:02:49,910
Well, what if we tried this?

48
00:02:49,910 --> 00:02:52,100
Select Max pages.

49
00:02:52,830 --> 00:02:53,700
Comma.

50
00:02:54,210 --> 00:02:54,510
Hey.

51
00:02:54,510 --> 00:02:55,200
Go away.

52
00:02:55,380 --> 00:02:56,250
Comma.

53
00:02:56,460 --> 00:02:58,830
Title from books.

54
00:02:59,430 --> 00:03:03,000
Well, we have another one of these errors that we saw earlier.

55
00:03:03,000 --> 00:03:04,350
At the end of the previous video.

56
00:03:04,350 --> 00:03:11,550
I talked about how whenever we aggregate things, we can only work with the data that they have in common.

57
00:03:11,730 --> 00:03:14,340
And in this case, we didn't aggregate ourselves.

58
00:03:14,340 --> 00:03:15,600
We didn't use Group VI.

59
00:03:15,630 --> 00:03:18,480
But behind the scenes, there's an aggregated query.

60
00:03:18,480 --> 00:03:24,000
I mean, this is an aggregation function and essentially it doesn't allow us to do what I'm trying right

61
00:03:24,000 --> 00:03:24,390
here.

62
00:03:24,570 --> 00:03:26,220
It's saying which title.

63
00:03:26,220 --> 00:03:30,660
It's looking at all the pages, it's aggregating, it's finding the max, but it doesn't know which

64
00:03:30,660 --> 00:03:34,470
title to display because it only gives us a single value back.

65
00:03:34,500 --> 00:03:39,420
It's not smart enough or it's not set up in such a way to give us the title of that book.

66
00:03:39,660 --> 00:03:41,400
But that doesn't mean we can't get it.

67
00:03:41,400 --> 00:03:45,420
We just have to do some extra work and we'll talk about that in the next video.