TOP Clause

Quick reference

• 00:04 In this video, I wanna talk about the TOP clause, and the TOP clause, let's just
• 00:09 limit the number of records that we return from any given SELECT statement.
• 00:13 So, if we go ahead and just get rid of all of this stuff, and it's very easy,
• 00:18 you just type in TOP and then give a number.
• 00:21 Let's say we want the top three and we wanna list everything from our database.
• 00:26 So if we execute this, we get John, Tina, and Mark, it's just the top three listed.
• 00:30 And if we get rid of this top statement and just run the thing as normal, so
• 00:36 John, Tina, Mark John, Tina, Mark, they're just the first three listed, all right?
• 00:43 So that's what the top does, and in our specific case here,
• 00:47 we've only got six records so we really don't need to limit the number probably.
• 00:51 But like I've said many times in this course, in real life, you could
• 00:54 have thousands, tens of thousands, millions of records in your database.
• 00:57 And speed becomes an issue, you may only wanna return a certain portion,
• 01:02 a certain amount from any query and a top clause will help you to do that.
• 01:08 So we've seen we can do top two or whatever, we can also use percent, so
• 01:13 let's say top 50% of everybody.
• 01:16 If we execute this,
• 01:18 again we're just gonna get the first three because there's six records.
• 01:21 50%, half of it is three and we could easily go the top 20%,
• 01:25 just get two, we could go 80%, whatever you like.
• 01:29 You could see how it changes, all but the last one, so that's top, now you
• 01:33 can use this also in connection with like a WHERE statement or a WHERE clause.
• 01:38 So let's say we want the top two, get rid of that percent
• 01:44 from our database where age is less than, say 35.
• 01:49 So if we execute this, we get Tina and Steve, Tina is 29,
• 01:55 Steve is 32, these are the top two of this category.
• 01:59 And you'll notice I didn't put the brackets around age, probably should,
• 02:02 as you can see, it works either way.
• 02:03 This way just allows you to keep things visually separated,
• 02:08 easier to look at at a glance.
• 02:10 Not such a big deal when we've got a simple select statement like this.
• 02:12 But sometimes, your select statements can get quite large and
• 02:16 it's easy to get lost inside of these things.
• 02:19 So these brackets help keep things separated, and
• 02:22 it's just a good thing to do.
• 02:24 So anyway, we've got two records that we've returned, now if we get rid of this
• 02:27 top clause and we just return everybody who's less than 35.
• 02:30 Now if we execute this, suddenly now we have four records, so
• 02:35 we do have more than the two that we just saw that people that are less than 35.
• 02:40 But if we wanna, we could do a top 50% like we did
• 02:45 earlier and again get the Tina and Steve records.
• 02:49 So mix and match, you can a trial, you can
• 02:54 order by age descending like that or
• 02:58 DESC capitalized you the way so high to low, we can go ascending.
• 03:07 Low to high and you will see the records change, the numbers,
• 03:11 the ones that we return because we are ascending.
• 03:14 And then doing the top 50% in ascending order so
• 03:16 it changes the output, so very interesting, something to keep in mind.
• 03:20 Anyway, that is the top clause, very useful,
• 03:23 in the next video we'll look at the select distinct clause.

Lesson notes are only available for subscribers.