Welcome to the Software Carpentry screencast on Databases. This screencast is on sorting results from a databse.
As we've seen in a previous screencast, we can use the SELECT statement to pull out columns from a table…
SELECT * FROM Experiments;
… but the results of our queries are returned in an arbitrary order—they are not sorted by default.
If we wanted to sort them by the Project, we add an ORDER BY clause to our query,
SELECT * FROM Experiments ORDER BY
and list the column we want the sorting to be done on, in this case, Project, and then we put ASC, an abbreviation of ascending, to specify that we want the results to be sorted in ascending order.
SELECT * FROM Experiment ORDER BY Project ASC;
When we run the query you see that the results are now sorted in ascending order by the Project field.
If we wanted the sorting to be done in descending order, we put the keyword DESC, short for descending, after the sort field,
SELECT * FROM Experiment ORDER BY Project DESC;
If we want to sort the results by the Project, and then by the Hours, we list both field names after the ORDER BY clause, separated by commas. So,
SELECT * FROM Experiment ORDER BY Project DESC, Hours ASC;
In the database system we are using, SQLite, ascending order is the default. That is, if we don't specify the sort order we get the results in ascending order,
SELECT * FROM Experiment ORDER BY Project, Hours;
We do not have to have selected the column in order for us to sort by it.
For instance, if we SELECT the LoginID, Project, and Hours spent
SELECT LoginID, Project, Hours FROM Experiment
we are able to order the results by the ExperimentDate, even though we haven't selected it.
... ORDER BY ExperimentDate
We are able to do this because ExperimentDate /is/ in the table we are reading from, we just chose not to return it's value in the results.
We can even sort the results by the value of an expression. In SQLite, the RANDOM() function returns a psuedo-random integer, as we see here:
SELECT *, RANDOM() FROM Experiment
(tip: run the query twice)
The values change each time we run the query.
So to randomise the order of our query results, we can simply sort them by the value of this function which, because it returns random values, will cause the ordering of the results to be random:
SELECT * FROM Experiment ORDER BY RANDOM()
In previous screencasts we've seen how you can select columns, append new calculated columns, and filter the results.
All of these operations can be combined into one query.
SELECT *, ROUND(Hours * .1, 1) FROM Experiment WHERE Hours >= 3 ORDER BY ExperimentDate DESC;
The SELECT clause lists the columns we want to retrieve. We'll use the asterisk as a shortcut for all of the columns in the table, and an expression that calculates 10% of each hour
The FROM clause tells the database which table to fetch the records from.
The WHERE clause specifies the conditions the records have to meet in order to be included in the results.
And, finally, the ORDER BY clause is used to specify how the results should be sorted.
The order that you see the clauses in here is required by SQL. The SELECT must come before the FROM, the WHERE clause must come after the FROM, and ORDER BY clause comes last.
In this screencast we've seen that you can select data from a table, and sort the results by fields in your tables, or with expressions, and we've seen that the selecting, filtering, and sorting operations can all be combined into a single query.