Welcome

• Welcome to the Software Carpentry screencast on Databases. This
  screencast is on sorting results from a databse.

What is a database?

• 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;


Sorting by unseen columns and expressions

• 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()


Putting it all together: Filtering and Sorting

• 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.

Conclusion

• 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.