Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
So let's take the idea of filtering our data a bit further. In this very simple SQL statement I am retrieving all the product information from the Product table here, and I have got a lot of different things in different colors, with names and Product IDs and list prices. What I would like to do is go buy the cheapest to the most expensive. Well it's yet another SQL clause and we'll put it right at the end of the SQL statement, and I'll just put in Order By. Well, order by what? Well, in this case I'd like to Order By ListPrice.
I hit F5 to execute that and we are going with the ordered ListPrice. By default it will be ascending. If we want to make it descending we just type-in the word DESC afterwards, hit Execute and now we have by ListPrice descending. You can if you want to pick multiple columns to order on, so I could order for example by Color, ListPrice, and what this would do is order by color ascending and then ListPrice descending within it. So if I execute that we have first off all the Null colors because that will come before any values and they are ListPrice descending, and then black and then blue and multi and red and so on.
You can of course combine this with the WHERE clause to filter these even more. So if I wanted to just ask for Color = 'Red', well I now don't really need to order by color, but just have the red products ordered by ListPrice descending and we get 38 rows instead of nearly 300. Now a lot of the times when you start to order your data you might even not be interested in the full amount. So say in this case when I am ordering by ListPrice descending I might not be interested in the fact that there is 295 of them.
But I might be interested in the most expensive 10 or the most expensive 20. What we can do if we only want to have say ten results is I can put right after the SELECT TOP 10. Now I'll split this up onto a separate line. It doesn't matter if you kept it altogether but this might be a little easier to read. So select the top 10 ProductID, Name, Color, and ListPrice from the Product table, order by ListPrice descending. It will do the ORDER BY first and then just return the top ten values, so in this case we are getting ten rows back.
Quite similarly you can also go, let's say if we wanted the top quarter products, we could say TOP 25 PERCENT, hit F5, and in this case we are getting about 74 rows and seeing as the amount of total rows is nearly 300, that looks about right. And very simply one of the ways we could actually get the first most expensive list price we could go and use the MAX function, but in here I am just going to say SELECT TOP 1. We'll find the most expensive product.
Now it is possible here that there could be multiple ones with that exact same [00:03:05. Even though we are ordering by ListPrice descending it's really going to be a bit of the luck of the draw which one we get if we are limiting it to only one result, and it's more about how that data will be internally stored in the table. But it can certainly be very useful. And that's how you start to sort your results.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.