1 00:00:00,150 --> 00:00:08,280 Next up, we'll look at a few more of these window only functions We saw rank and very similar to rank. 2 00:00:08,280 --> 00:00:09,960 We have row number. 3 00:00:10,050 --> 00:00:15,150 The main difference here is that rank allows for ties as we discussed. 4 00:00:15,720 --> 00:00:19,290 You can have multiple things ranked one if they have the same value. 5 00:00:19,320 --> 00:00:23,910 Multiple things can be ranked four as we see here because they have the same value. 6 00:00:24,060 --> 00:00:26,450 And so we end up skipping numbers potentially. 7 00:00:26,460 --> 00:00:32,880 Then we have row number, which is just going to label each row within a partition, starting with one 8 00:00:32,880 --> 00:00:36,950 and going sequentially one, two, three, four, five, six, seven, counting the rows. 9 00:00:36,960 --> 00:00:42,450 So it doesn't care if the values have a tie or anything, it just counts the rows so it doesn't even 10 00:00:42,450 --> 00:00:43,440 look at the values. 11 00:00:43,470 --> 00:00:45,090 I'll show you an example of that. 12 00:00:46,050 --> 00:00:54,870 If we just did alongside the department salary rank, I'll also do let me just highlight an example 13 00:00:54,870 --> 00:00:55,290 here. 14 00:00:55,290 --> 00:00:57,090 Like within sales, right? 15 00:00:57,210 --> 00:01:04,620 We have the highest ranked in sales, 160,000, second, third and then fourth rank is a tie, 61,060 16 00:01:04,620 --> 00:01:05,340 1000. 17 00:01:05,340 --> 00:01:08,640 And then that just jumps to sixth rank and seventh ranked. 18 00:01:08,820 --> 00:01:15,660 So if I do the same thing except instead of rank, I will do row number. 19 00:01:16,880 --> 00:01:19,510 Partitioned by department, order by salary. 20 00:01:19,520 --> 00:01:24,770 We set up our different windows, we order them how we want, and then we just say, number them in 21 00:01:24,770 --> 00:01:27,650 order and I'll go with department salary. 22 00:01:28,520 --> 00:01:29,120 I don't even. 23 00:01:29,120 --> 00:01:29,810 Yeah, I don't know. 24 00:01:29,810 --> 00:01:30,800 Just row number. 25 00:01:30,800 --> 00:01:31,310 Sure. 26 00:01:32,560 --> 00:01:34,250 It's simple enough. 27 00:01:37,030 --> 00:01:38,140 And what did I. 28 00:01:38,170 --> 00:01:39,700 Am I missing a comma somewhere? 29 00:01:40,750 --> 00:01:41,680 Oh, duh. 30 00:01:41,710 --> 00:01:45,340 I can't use rule number as a name there, because that is the name of a function. 31 00:01:45,340 --> 00:01:49,060 So let's call it department row number. 32 00:01:51,550 --> 00:01:52,570 And here we go. 33 00:01:52,690 --> 00:01:54,510 So let's take a look again at sales. 34 00:01:54,520 --> 00:01:56,110 This is our ranking here. 35 00:01:56,110 --> 00:01:58,870 One, two, three, four, four, six, seven. 36 00:01:58,870 --> 00:02:00,520 And this is our row number here. 37 00:02:00,520 --> 00:02:03,580 One, two, three, four, five, six, seven. 38 00:02:04,320 --> 00:02:06,180 All right, So that's row number. 39 00:02:06,300 --> 00:02:10,500 We also have another thing that's somewhat confusing, which is dense rank. 40 00:02:10,530 --> 00:02:16,080 Dense rank is like rank returns to rank at the current row within its partition without gaps. 41 00:02:16,080 --> 00:02:22,380 So when we have peers that are ties, they get the same rank, but then the function will assign consecutive 42 00:02:22,380 --> 00:02:26,070 ranks to peer groups, meaning it won't skip any values. 43 00:02:26,070 --> 00:02:29,700 So this is really a minor detail. 44 00:02:29,700 --> 00:02:32,790 I mean, it's important, but it's not that essential that you know this. 45 00:02:32,790 --> 00:02:34,590 But I'll show you what that looks like. 46 00:02:34,590 --> 00:02:39,780 Let's do the overall salary rank here and then rank. 47 00:02:40,810 --> 00:02:50,420 Dent rank, dent rank over order by salary as overall salary dense rank. 48 00:02:50,440 --> 00:02:52,960 It's a long column named Let's see if it all fits in our screen. 49 00:02:52,960 --> 00:02:54,430 I think it would have to zoom out. 50 00:02:55,090 --> 00:03:01,240 Or maybe we can just rename this a tiny bit like we'll call this overall dense rank. 51 00:03:01,540 --> 00:03:03,340 And this is overall rank. 52 00:03:04,630 --> 00:03:09,040 So let's run this paste you in here. 53 00:03:09,070 --> 00:03:10,710 Yeah, I have to zoom out. 54 00:03:10,790 --> 00:03:11,860 Okay, here we are. 55 00:03:11,950 --> 00:03:13,180 Overall rank. 56 00:03:13,570 --> 00:03:19,660 Let's order by that so you can see order by overall rank. 57 00:03:20,450 --> 00:03:23,510 They'll make it easier to verify what's happening. 58 00:03:24,170 --> 00:03:27,740 So we have first, second, third, fourth, fifth, sixth, seventh. 59 00:03:27,740 --> 00:03:29,120 We have a tie, right? 60 00:03:29,120 --> 00:03:31,460 And then after seventh, they're both 70,000. 61 00:03:31,730 --> 00:03:38,180 After seven, we have ninth rank and then ten and we have a tie for 11, 11, 11, 11. 62 00:03:38,180 --> 00:03:44,870 We have three employees with 61,000 salaries, and then it jumps right to 14th after that. 63 00:03:45,460 --> 00:03:47,730 And if we look at dense rank instead. 64 00:03:47,740 --> 00:03:49,360 One, two, three, four, five, six. 65 00:03:49,360 --> 00:03:50,650 There's a tie at seven. 66 00:03:51,010 --> 00:03:54,120 So two people have that salary again, same deal. 67 00:03:54,130 --> 00:03:57,880 But then it resumes counting the rank at eight. 68 00:03:57,880 --> 00:04:01,990 So it doesn't skip any places like regular rank does. 69 00:04:02,020 --> 00:04:03,940 So we have rank versus dense rank. 70 00:04:03,970 --> 00:04:11,200 The highest dense rake in this example is 18th place versus 21st here for regular rank. 71 00:04:11,200 --> 00:04:16,360 And then we have row number, which right now we're using on a department basis. 72 00:04:16,360 --> 00:04:18,519 So it's hard to compare apples to apples. 73 00:04:18,519 --> 00:04:21,940 But if I also add it in overall row number. 74 00:04:22,870 --> 00:04:23,980 We'll do Roe. 75 00:04:25,540 --> 00:04:31,910 Underscore number over order by salary as overall number. 76 00:04:31,930 --> 00:04:34,460 Let's just call that overall number or num. 77 00:04:34,510 --> 00:04:36,700 I'm going to have to zoom out even further. 78 00:04:37,120 --> 00:04:38,830 These column names are getting long. 79 00:04:41,480 --> 00:04:42,500 Zoom, zoom, zoom. 80 00:04:42,950 --> 00:04:48,500 You'll see it goes up to 21 as the overall number, all consecutive, just counting each row. 81 00:04:48,590 --> 00:04:53,180 Overall rank goes up to 21, but it skips some numbers whenever there's a tie. 82 00:04:53,180 --> 00:04:58,100 And then overall dense rank does not skip any numbers if it has a tie. 83 00:04:58,130 --> 00:05:03,770 They share that rank and then it just resumes with the next consecutive number, meaning we only have 84 00:05:03,770 --> 00:05:05,900 18 as the highest instead of 21. 85 00:05:06,500 --> 00:05:06,890 All right. 86 00:05:06,890 --> 00:05:08,570 That's probably more than you ever want to know about. 87 00:05:08,570 --> 00:05:11,480 Rankings and row numbers and dense rankings. 88 00:05:11,510 --> 00:05:14,810 Next, we have a few more of these window functions I want to cover.