Data aren't always entered in the sequence you need. Find out how to reorder the results of your query.
- [Instructor] When we get information back from a query, it's usually in an order that isn't always helpful. So to sort data, we can use the "order by" keyword. Let's set up a statement that will give us a lot of information back here. I'll write "select first name, last name, from people." There's my thousand rows and I can see that it's all over the place in terms of sorting. There's no pattern to the first name or to the last name.
This is the order that the records are in the database. Sure, but that's not how I want to see them. I'll add an "order by" clause and I'll need to give it a field to sort on. I'll add "first name" for now. There we go. The first names are alphabetical. These are sorted in an order called ascending, which means that the values start small and get larger. And that's the default.
I can also tell the database to give me ascending order by adding the keyword "ASC" in the "order by" clause. Or, I can switch up the order to descending, with larger values first, with the "DESC" keyword. These are a little bit confusing to me sometimes because when I think of something ascending, I think of it going up. But the larger values, what I would associate with letters later in the alphabet, end up below smaller values in the list. So, a helpful trick for me is to remember that in ascending, "A" will come closer to the top.
And in descending, the letter "D" would come closer to the top than "A" would. We can add more fields after the first one as well if we want to add a secondary or tertiary sort order. For example, we could sort first by state and then within the state, sort by last name. I'll write a new query with "select state, last name, first name, from people, order by state, ascending, last name, ascending." I'll run that.
And here I have the data listed alphabetically by state, and then, within that, for each state the data are sorted by the last name in alphabetical order. I'll change the last name sort order to descending here. And I'll run it again. And now, even though the states are in alphabetical order, last names are in reverse alphabetical order. Take some time to explore the data now that you're able to change the sort order of results.
- Name the predicate of the following statement: SELECT EyeColor, Age FROM Student WHERE FirstName = 'Tim' ORDER BY LastName ASC;
- Explain what to use to enforce the order in which an expression must be evaluated if the WHERE clause contains multiple expressions to evaluate.
- Identify the best option to join two tables in a database to be able to display data from both.
- List a data type that is not numeric.
- Determine the result of running the following statement on a table containing columns col_1 and col_2:
- INSERT INTO Box (col_1, col_2) VALUES ('A', 'B'), ('A', 'B'), ('A', 'B'), ('A', 'B');
- Determine the best approach of deleting Jon Ramirez (ID 3452) from a Student table.