How to Use Select Top in SQL Server

i'm your host sean mckenzie thanks for joining me once again on my channel on data engineering in this episode we go back to our data engineering playlist and we're going to take a look at sql servers select top which is a nice way that allows you to select just the top few records from a particular query and so you can select the top one record or the top 10 records or 100 records and we're also going to learn about one thing that you probably didn't know about select talk before you started using it which is very important for doing queries so without further ado let's get to our select talk in sql server need help for coaching on your project make sure to check out my patreon the link is in the description okay so i've got a blank worksheet here i'm using sql server management studio and uh what i'm going to do is i'm using the adventureworks database which is one of the free ones that you can get from microsoft to do testing and things like that and to to learn on and uh so what i'm going to do is i'm going to just show you this product table here so i selected star from product sales lt.product and you can see it's got all these rows in it there's 295. in real life you you wouldn't do a select star on a production table you would always use something like select top because if there's 2 million records in there well it's going to sit there you know while you're trying to look at something and and it's going to get too many records it's going to take forever and and who knows it might even you know lock up on you depending on on your environment so uh but there's a couple tables here there's a sales order detail table and there's a product table i guess i'll take the i'll take the product table that seems to be an easy one for our discussion today and so what i'm going to do is i'll paste that one in there and now what we can do is in the most simple case uh we're going to say you know select top one from sales lte.product and what it's going to do is based on the database settings for for sorting and how the you know the table is configured you'll get the first row of that table or if you change it to 10 you'll get the top 10 rows of that table and that's really really handy or you can do say you know top 100 and so on and so you can see the 100 rows down at the bottom right there in the count of the output so when i hit f5 you know it goes and gets the data it comes back tells me the count at the bottom right there you can also you can so just like any other query you can say select top 100 and you don't have to get everything you can get just the fields that you want so say i did select top 10 you know of the product number so you might have like a top 10 list or something like that of your products or you know sales people or whatever um so in this case uh that you know that's where you could use top 10 quite handily um and uh you know we can throw on a list price there as well and i'll sort of uh structure this query a little bit more nicely and now there's a there's an explicit or an implicit meaning though when we say select top um we're getting the database order of it and so one very important thing that work we need to do when we do select top is to make sure that we specify an order an order by clause on the end there and so now you can see that these are the top priced items in the list and you'll note that by using order by it actually with the combination of order by with top it actually becomes like a where clause so it actually changes the filter of the list the almost like a where clause and so that can be very very powerful and that's what one thing that a lot of people don't know they just say you know select top 10 of this but actually if you change the order by it changes the records that are returned in the list and so that as you can see there's starting at w and going back through t uh from you know uh women's tights down to touring 3 000 yellow 58 you know that's a very very powerful thing that we use when we use top now if you're familiar with other databases like oracle or mysql or some other databases like that you're probably familiar with using the word limit instead and so this top is very very similar to limit limit you always do at the end of your query when you're in those other systems but in in sql server we do it at the beginning with top so this obviously won't work because this is sql server but you would do something like you know limit three on the end of your query there but and so that is how you can use select top in sql server hope you enjoyed today's discussion on how to use select top and sql server if you like what you saw today please give the video a thumbs up make sure to subscribe to the channel click the bell when you see the bell and if you have any questions or comments put those in the comment section below have a great day have a safe day and i'll catch you next time

Channel: Sean MacKenzie Data Engineering

Views: 104

Keywords: How to Use Select Top in SQL Server, sql select top, select top, tsql top, sql top with order by, sql server, sean mackenzie, sean mckenzie data engineering, sean mackenzie data analytics

Id: whVPkXKPTVg

Length: 5min 47sec (347 seconds)

Published: Sat Oct 30 2021