1. Ordering and Filtering
We've seen how to limit the number of rows returned using TOP. Now, we'll learn more advanced filtering and sorting, using WHERE and ORDER BY.
2. Order! Order!
Tables comprise of rows and columns, and you will usually see your query results displayed in a tabular format.
But we should bear in mind that queries return specific SETS of data that meet the conditions we specify , and that sets have no default order.
There is no guarantee our query will return the results in the same order each time.
However, we can make this happen using ORDER BY.
3. Order by - ascending order
To order our query results, we write ORDER BY below FROM, then list the columns we want to sort by.
Here, we sort by year_intro, then the product_id column.
Notice rows 4-6, and 9 -10, have the same values in the year_intro column, so the product_id column determines their final row position
We can ORDER BY any column - including columns that do not appear in the SELECT part of the query.
4. Order by - descending order
Here we add DESC after the year_intro column in the ORDER BY clause. This sorts the results in descending order.
5. Order by - multiple columns, different directions
We can sort by multiple columns.
On the left,the columns are first sorted by year_intro in descending order, and then appearances in ascending order.
Year_intro is listed first in the ORDER BY clause, so it takes precedence when sorting the rows.
Then the appearances column is sorted in ascending order, for each year.
On the right, both columns are sorted in descending order.
6. Ordering text
We can also order string or text fields.
On the left we see name_alias sorted from A-Z, and from Z-A on the right.
7. Applying criteria to filter results
You won't usually want to return all the rows in your table. You will have specific information you need to extract. For this, you need to use the WHERE clause, which we place underneath the FROM section of the query.
8. The WHERE clause
We use the WHERE clause to ensure we only return rows that meet our desired criteria, as shown in the following examples.
We can filter based on numeric values, and also strings or dates.
We use single quotes to specify the text or date value we want to use as our filtering criteria.
As with ORDER BY, we can use a column that is not in the main SELECT statement, as part of our WHERE clause.
9. Not equal
You can test for non-equality using the left and right arrows together.
10. Between
You can use BETWEEN to return values in a range.
This is inclusive - this query will return every available value from 20 right through to 30.
You can negate this by prefacing BETWEEN with 'NOT', meaning these values will be excluded from the results.
11. What is NULL?
NULLs occur when there is no value for a particular field, for one or more records.
Some columns are not allowed to have missing values.
However, it may be perfectly valid for other columns to contain NULLs.
A missing value is not necessarily a zero value, and we may need to be aware of the 'missingness' of the record - NULLS help highlight gaps in our data.
12. Working with NULLs
It's very useful to know how to retrieve NULLS, using IS NULL and how to filter them out, using IS NOT NULL.
13. Let's sort it!
OK, we've covered a lot. Let's go and try this out!