The top 10 records based on your sort order so let's get started when you've written some queries in sequel server one of the really useful things you can do is to change the order of the results.
So here I've written a very basic query that selects three fields from a table of films having executed the query already I can see that the results appear in apparently no particular order.
At all they've actually appeared in the order that the films were added to the database so I'd like to change that there are several more useful ways I can view that data so to do that I'm going.
To add the order by clause to my query or by two separate words in the order by clause you can there are two things you can either list the name of a field or an alias that you've used for that field.
Necessary well it will work in ascending order by default anyway so if I execute this query now my films come out in alphabetical order I've just shown you how that you could use the ASC keyword.
To do exactly the same thing so if I execute again I get no change in the results but more useful you can also use de SC to put things in descending order if I execute the query now I see things.
In reverse order that's how you do straight forward sorting for query results you can also sort the results of a query based on more than one field at a time.
So to demonstrate this I'm first of all going to change the sort order of my query from using film name I'm going to use duration instead motive relations into built-in field name is an alias.
That I gave to a field earlier that's perfectly acceptable so I run this query now I can see that the longest film appears at the top and they go in descending order of duration what I'd.
Posts Related:
Like to ensure is there any films that
Have the same duration are sorted alphabetically by name so if I scroll down I can find that numbers 18 and 19 have the same duration 167 minutes but.They're sorted reverse alphabetically so I'd like to ensure that the next time round my film names are sorted in ascending order so you simply add extra fields to the order by clause in the.
Same way you happen to the Select list you type in a comma and then type in the next field name or alias that you've used so if I execute this query now and scroll down again to numbers 18 and 19.
And we can find they've switched around one last slightly unusual thing about the order by clause is that you can include fields in here that aren't included in the Select list so to.
Demonstrate that I'm going to add the film Oscar wins field to the order by clause I like to sort those in descending order when I execute this query the order of my films comes out in.
A particular order is not based on title or release date or generation it's based purely on film Oscar wins even though that isn't displayed in the results so just to reassure you that that is.
Actually working if I add in the film Oscar wins field into the Select list and execute the query again you can see that that actually is what's happening the highest Oscar wins are at the top of.
The list and they go in descending order one of the useful thing that I can do with sorted records it's a display only so many of them I can do that by using the top 10 clause it's actually a bit of.
A misleading term it's not just the top 10 records I can select the top any number of Records if I say select top five I'll get five records here I'm gonna go for the top ten now the results.
That I get are based purely on the order
That the results are sorted by so if I execute this query now I'll simply get the top ten films from previous order slightly misleadingly it's not a.Guarantee that there are more films with the same number of Oscar wins as the tenth one in the list so to make sure that you get those as well you can add in the two keywords with ties having.
Done this if I execute the query again I'll see that I actually get more results than I previously had so as you got 13 altogether not because there were three extra films which had the same.
Number of Oscar wins as the tenth one in the list the the top ten option is based purely on the sort order at the records in a table so if I wanted the opposite of this if I wanted that the top 10.
Least successful films then what I had to do is not add the word bottom ten I have to simply change the sort order of my query if I change this to ascending sort orders so again remembering that.
I'm trying to find the top 10 and then execute this query I get one record in them that there isn't actually filled in at all confusingly and then there are actually two hundred records returned.
Where the number of Oscars one is zero so there you go that's how you can use the top 10 Clause to affect the number of records if you've enjoyed this training video you can find many more.