1 00:00:00,090 --> 00:00:02,490 Next up, we're going to talk about first value. 2 00:00:02,520 --> 00:00:08,460 First value, according to the docs returns the value of whatever expression is in the parentheses from 3 00:00:08,460 --> 00:00:10,650 the first row of the window frame. 4 00:00:11,040 --> 00:00:16,490 So when we have these different windows within each one of them, they'll be in some order, right? 5 00:00:16,530 --> 00:00:21,140 We can order them by whatever we want using order by instead of over. 6 00:00:21,150 --> 00:00:27,360 And then we can ask for the first value of some particular column from that first row. 7 00:00:27,750 --> 00:00:35,610 So this is how we could do something like let's take our basic employee number department salary that 8 00:00:35,610 --> 00:00:42,360 we're selecting and then do something like first value employee ID. 9 00:00:43,520 --> 00:00:44,420 Over. 10 00:00:44,450 --> 00:00:51,290 And then I'll say sort of by salary, I guess, descending. 11 00:00:51,890 --> 00:00:59,900 So what this is going to do is divide it just like over always does divide up all the rows in our case 12 00:00:59,900 --> 00:01:00,680 into one window. 13 00:01:00,680 --> 00:01:01,910 There's no partitioning. 14 00:01:02,030 --> 00:01:05,840 So one window with all the rows order them by salary. 15 00:01:06,020 --> 00:01:08,960 So the first row is going to have the highest salary. 16 00:01:08,960 --> 00:01:13,720 And then what we're saying is we want the employee ID So I could put something else in there too. 17 00:01:13,820 --> 00:01:18,920 You know, this is how I could get the last name of the highest paid person in each department or something 18 00:01:18,920 --> 00:01:19,420 like that. 19 00:01:19,430 --> 00:01:23,540 What we're doing right now, though, since we don't have names, it's just getting the employee number 20 00:01:23,540 --> 00:01:25,460 of the highest paid employee. 21 00:01:26,120 --> 00:01:30,710 Let's paste this and Oh, geez, it's EMP number. 22 00:01:30,710 --> 00:01:32,390 No, not ID. 23 00:01:33,590 --> 00:01:36,680 And now you'll see that we have ten in this column. 24 00:01:36,680 --> 00:01:38,360 I didn't give it a name, so it's real long. 25 00:01:38,360 --> 00:01:41,000 But every single row just says ten, ten, ten, ten, ten. 26 00:01:41,000 --> 00:01:47,630 And that's because that is the employee number of the row that has the highest salary because we ordered 27 00:01:47,630 --> 00:01:52,760 by salary within our one massive window descending, so highest to lowest. 28 00:01:52,760 --> 00:01:57,590 And then we took the first one of those rows and got the value of employee number. 29 00:01:58,100 --> 00:02:00,950 So here's another version of that where we partition. 30 00:02:00,950 --> 00:02:02,540 Let's do first value. 31 00:02:02,570 --> 00:02:07,790 Get the employee number where we partition by department. 32 00:02:09,169 --> 00:02:11,210 The apartment. 33 00:02:12,160 --> 00:02:16,660 And we'll order by salary descending again within each department. 34 00:02:16,870 --> 00:02:20,980 And let's call this one as highest. 35 00:02:22,070 --> 00:02:28,190 Paid in dept and this one will be as highest paid overall. 36 00:02:28,580 --> 00:02:33,290 So this is going to give us the employee number of that highest paid person. 37 00:02:35,400 --> 00:02:40,440 And you'll see within highest paid department we now see. 38 00:02:40,440 --> 00:02:40,830 All right. 39 00:02:40,830 --> 00:02:45,450 Within the sales department, the highest paid person has idea of ten employee number ten. 40 00:02:45,450 --> 00:02:46,290 That makes sense. 41 00:02:46,290 --> 00:02:53,040 159,000 within engineering, the highest paid person that has the ID of four or employee number. 42 00:02:53,040 --> 00:02:56,970 I keep saying ID, but it's employee number four, which is 103,000. 43 00:02:57,960 --> 00:03:00,300 And then we have to go to customer service. 44 00:03:00,300 --> 00:03:06,660 The highest paid person has ID of 17 because employee number 17 right there, 61,000. 45 00:03:07,260 --> 00:03:13,920 So to recap how that works, once we have our over and whatever partition by may or may not be there, 46 00:03:13,920 --> 00:03:19,500 we have one window or maybe multiple windows and then within those windows the rows can be ordered. 47 00:03:19,500 --> 00:03:24,660 And then once that's done, once the splitting into windows is done and they're all ordered within each 48 00:03:24,660 --> 00:03:28,500 of those windows were then taking from the first row the employee number. 49 00:03:28,500 --> 00:03:30,390 But of course you could put anything else in here. 50 00:03:30,390 --> 00:03:35,190 If you had more complicated data, you could put whatever you want in there, some expression that is 51 00:03:35,190 --> 00:03:37,620 based on that first row within each window. 52 00:03:37,650 --> 00:03:40,710 We also have last value, same thing. 53 00:03:40,950 --> 00:03:42,810 It's just going to work the other way around. 54 00:03:42,810 --> 00:03:48,810 And we have nth value where you can specify after the expression you want, the second or the third, 55 00:03:48,810 --> 00:03:54,720 the fifth, the 90th, whatever you want, the nth row of that window frame. 56 00:03:54,750 --> 00:03:59,640 Take the expression from that row so I won't waste your time and show an example of that. 57 00:03:59,640 --> 00:04:01,770 It's the same exact concept, though. 58 00:04:01,770 --> 00:04:04,950 You just have a different number, a different position you're looking for.