LibreOffice Base SQL: Ordering Results

Today, I will continue the discussion of SQL queries with the ordering of query results. The SQL in this post is based on the “TeamMembers” database. For instructions on setting up the database in LibreOffice and a short description of the database and tables, see the post at http://wp.me/pvwht-a4.

The ORDER BY Clause

Getting information from the database tables is great, but often you will need to sort them, giving them more meaning or making them easier to understand. The ORDER BY clause allows you to do just that. The ORDER BY clause always goes at the end of the SELECT statement.

Here is a simple example of a SELECT with an ORDER BY:

SELECT "FirstName", "LastName"
FROM "TeamMembers"
ORDER BY "LastName";

This query will create a data set containing all the team members in the table sorted by last name. By default the sort is done in ascending (ASC) order. To reverse the order add descending (DESC) sort to the ORDER BY.

SELECT "FirstName", "LastName"
FROM "TeamMembers"
ORDER BY "LastName" DESC;

ORDER BY Any Field in the Table

You do not have to include your sort field in the SELECT list. You can use any field in the table to sort the records.

SELECT "FirstName", "LastName"
FROM "TeamMembers"
ORDER BY "DateOfHire";

This query selects the first and last name of all the team members and sorts them by their date of hire. Note that the DateOfHire field is not in the select list. If you need confirmation that the sort was done correctly, add the DateOfHire field to the select list to see if the order of the resulting records change (it will not).

ORDER BY More than One Field

You can also sort by more than one field. The query will first sort by the first field given in the ORDER BY clause. If any of the first fields are the same, it will then sort them using the second field in the ORDER BY list. If you have a third field, it would sort if the first two were the same, etc.

SELECT "FirstName", "LastName"
FROM "TeamMembers"
ORDER BY "LastName", "FirstName";

This query selects the first and last name of all the team members, then sorts them by last name and first name. In other words, Johnny Walker would come in the list before Sandy Walker.

Works with WHERE

Just like with other clauses and operators, you can use the ORDER BY with the other phrasing in your SQL.

SELECT "FirstName", "LastName", "DateOfHire"
FROM "TeamMembers"
WHERE "DateOfHire" > '2005-01-01'
ORDER BY "LastName", "FirstName";

This query selects all the team members who were hired after January 1, 2005, sorting the results by last name and then first name.

The ORDER BY clause allows you to sort the results of a query. You don’t have to use a field from the SELECT list, and you can sort your results by more than one field. The ORDER BY is always at the end of your query.

Next time, we will look into creating queries using more than one table.

Written with StackEdit.