00:00What's going on, everybody? Welcome back to another video. Today, we are continuing our
00:03Postgres SQL series by looking at order by, limit, and offset. Now, order by is the one
00:09that we're really going to focus on in this lesson because it's used all the time. Limit
00:13and offset I use in specific circumstances, and I'll explain that because that's what we'll look
00:17at at the very end. But order by is just a way of ordering your data in your output. So
00:24after we
00:24actually filter our data, after we select what columns we want, that's when we use order by.
00:29It comes at the very end. So let's come in here and let's just say we want to do where
00:36has both arms is equal to, and then we'll do yes. So we're going to go ahead and run this.
00:42And now we have our output, and this looks great. But if they have both of their arms,
00:47maybe you want to take a look at who has the most net worth with both arms. And that is
00:52absolutely
00:52something I'm curious about. So now we're going to come in here and we're going to go down. We're
00:58what we need to do is specify what column we're going to order it on. So we want to order
01:02on
01:02estimated net worth. And let's just go ahead and run this as is. So by default, we're looking at it
01:11in ascending order. Ascending means smallest to largest. So we have our smallest up top, 500,
01:17all the way down to our largest. Now what if we wanted to go the other way? What if we
01:22want it to
01:23be highest first down to lowest? We can specify this. That's going to be DESC, which stands for
01:29descending. So highest to lowest. Let's go ahead and run this. And now we've just reversed it. We
01:35now have the highest up here down to the lowest. Now although estimated net worth is by default
01:40ascending, if I am going to do it that way, I typically write it because I like to be explicit
01:44with my queries. I don't like to use defaults. That's just a best practice that I've come to learn
01:50over the years. It's better to write it explicitly so that there isn't anything that is just by default
01:55or might be misunderstood down the line. Or if a default changes, then if you save this query,
02:01this entire query could be changed later on. And so I like to be really explicit.
02:05So we're going to keep this descending right here because I think that works really well.
02:10But let's say I want to know, okay, there's other species here. We have droids. We have humans.
02:15I want to kind of break this up by species and then the estimated net worth. Well, we can do
02:21that.
02:21We can actually separate it right here. We're going to say species and then put a comma.
02:26And now I'll explicitly write this, but we're doing species ascending and then estimated net worth
02:33descending. That means we're going to take a look at the species and do it A to Z because that's
02:38lowest to highest alphabetically because now we're looking at text. And then next we're looking
02:43at the estimated net worth. And because this is numeric, we're doing it descending highest
02:47to lowest. Let's go ahead and run this. And now you can see our species is alphabetical.
02:53So we have droid, droid, gungan, human, human, human, all the way down. And then within the droid
02:58right here, we can see that within the estimated net worth, now it goes highest to lowest. So it's 2000
03:05and then 1500. If we go down to human, we can look at this right here. It goes human highest
03:12to lowest,
03:13but then it resets after each species. So droid highest to lowest, gungan, there we go, human all
03:20the way down, unknown, that's its own and wookie, it's its own, but it's ordering on the species first.
03:25And then a secondary order by is happening on the estimated net worth. You're going to use order by in
03:30almost all queries that you write, not all the time, but I would say 80 to 85% of queries,
03:36I like to order it. And so this is something that you use all the time. Let's get rid of
03:40this. And
03:41I'm just going to use the estimated net worth. Now we want to limit how many queries were actually
03:47returning. For example, I'm going to come down here and limit would come after the order by the order
03:53in your queries is actually important. You cannot put limit here, there is an order of operations on how
03:59things actually run on the backend when they process your query. So if you put limit, you know,
04:04at the top or something like that, it's just, it's not going to work. So there is an order of
04:08things.
04:08So we have a limit here. Let's say, I just want to look at the most wealthy person who has
04:13both arms.
04:15And since we ordered it by estimated net worth descending, we're going to limit to the one
04:20person with the highest estimated net worth. Let's go ahead and run this. And now we're going to see
04:25it's Padme Amidala. So she has an estimated net worth of 80 million or 8 million. It's high.
04:32She's the most wealthy person, but maybe I want to look at the top three. I'm going to limit to
04:36the top three wealthy people who have both their arms. So now we have Padme Amidala,
04:41Leia Organa, and Han Solo. We are simply limiting the rows in our output after all of these things
04:47already occur. Now there's one last thing that we're going to take a look at, and this is offset.
04:52I don't use this a ton. There's very few use cases where I use it, but I want to show
04:56it to
04:57you nonetheless. Offset just means it's going to skip a few rows before it actually shows you
05:02the limit. So let's go ahead and run this. So this is all of our output. What we're going to
05:09do
05:09is let's do an offset of one. So let's come right down here. We're going to say offset one. So
05:15we're
05:16going to skip this first row, and then we're going to limit it to the next three. So we should
05:20have
05:20Leia Organa, Obi-Wan Kenobi, and Han Solo in our output. Let's go ahead and run this.
05:26And there we go. Now one thing to note, and this is a very small note, is that the estimated
05:31net
05:31worth for Han Solo and Obi-Wan Kenobi is the same. And so you'll notice they actually switched
05:36the output rows before Obi-Wan Kenobi was above Han Solo. Now Han Solo is below Obi-Wan Kenobi.
05:42It's kind of switching up which row it's being on the output, but they are the same. If we want
05:47it to
05:48be more consistent, we would need to add an order by probably on the character name like we had
05:52before. But I just want to explain that really quickly. But that's what the offset does. And you
05:56can offset it by two, three, four, five, whatever number you want. But again, I don't use this one a
06:02ton. There's very few use cases where I've actually used it, but it is useful. And so I wanted to
06:07show
06:07it to you. I really hope that this lesson has been helpful. If you liked it, be sure to like
06:11and subscribe, and I will see you in the next lesson.
