The ORDER BY clause can be used within the EXECUTE, SELECT and VALUES statements to list records within a table within some desired order. ORDER BY is an important clause because records in tables should never be assumed to be stored in some specific order: if we want to see them in some specific order we must always explicitly specify that ordering using the ORDER BY clause.
ORDER BY <field> <options>, <field> <options>, ....
<field> - When used with SELECT each <field> can be an expression. When used with EXECUTE and VALUES each <field> must be a field name.
<options> - A list of options used by b-tree indexes, which can be any number of options or empty. Available options include:
ASC - sort in ascending order (default).
DESC - sort in descending order.
CASE - case-sensitive sort, only applies to text fields,
NOCASE - case-insensitive sort, only applies to text fields.
Inapplicable options are ignored, for example, specifying CASE for a numeric field.
SELECT Job, Name
FROM Invoices
ORDER BY Job DESC, NAME;
Manifold's ORDER BY can and does use indexes whenever possible, both for data within .map files and also for data within other data sources. If a data source specifies an index that matches the criteria in ORDER BY the Manifold query engine will use that index and will avoid fetching all data into intermediate storage and then sorting that fetched data locally.
Matching is significantly wider than just one-to-one. For example, if a sorted table provides an index on fields a and b, and ORDER BY requests sorting on a, then sorting will use the index despite the index using two fields instead of one. In the same table, if ORDER BY requests sorting on a in descending order and then sorting on b also in descending order then sorting also will use the index as well. CASE and NOCASE options must be matched exactly.
However, if ORDER BY requests sorting on a in ascending order and then sorting on b in descending order in that case sorting will not be able to use the index provided by the table and will have to create a temporary index locally. If ORDER BY requests sorting on b sorting will not be able to use the index provided by the table. If ORDER BY requests sorting on a+b sorting will not be able to use the index provided by the table.
Manifold takes a much more granular approach than non-parallel systems to running parts of queries, with stricter boundaries between parts given that different parts could possibly be run in parallel or on different data sources. As a result, ORDER BY and SELECT are in different nodes so ORDER BY cannot sneak any peeks into an alias list for SELECT. That means Manifold does not allow an ORDER BY clause to refer to aliases assigned in the SELECT list, for example, as in forms such as
SELECT [X (I)] X, [Y (I)] Y
FROM [Drawing] ORDER BY X, Y;
Instead, aliases must be replaced with the names of the original fields, as in
SELECT [X (I)] X, [Y (I)] Y
FROM [Drawing] ORDER BY [X (I)], [Y (I)];
If an alias is used to
name an expression, it must replaced with a copy of an expression. There
are ways to avoid duplicating an expression, of course, with the most
obvious way to use nested queries.
Consider the Invoices table seen at right. Each record has a Name, a Job classification, and a Payment. Names and job classifications include some repetition, for example, with the name Alex occurring in three records, twice with Electrical jobs and once with a Carpetry job.
A simple SELECT query:
SELECT Job, Name
FROM Invoices
ORDER BY Job;
The results table lists all records in the order specified by ORDER BY, that is, alphabetically by Job. Within each Job category records are unordered. For example, the first four records are all Carpentry records but the names are not in any order: Tina, Alex, Tom, and Tina.
We can order the table by two fields:
SELECT Job, Name
FROM Invoices
ORDER BY Job, Name;
The results table lists all records ordered alphabetically first by Job and then within each Job alphabetically by Name. For example, the first four records are all Carpentry records but the names are now in alphabetical order as well: Alex, Tina, Tina, and Tom.
Options such as DESC accompany the ordering field:
SELECT Job, Name
FROM Invoices
ORDER BY Job DESC, Name DESC;
The table now is ordered alphabetically in descending (Z first) order by Job and then within each Job in descending order by Name.
No brackets - In this topic to reduce visual clutter we do not use square [ ] brackets around simple field and table names. Square brackets are optional if the name is unambiguous.
Before ORDER BY - THREADS and BATCH should appear in a query before ORDER BY: both of these options continue working with ORDER BY. If the expressions in the SELECT list take a long time to compute then using more than one thread will very likely significantly shorten that time whether or not ORDER BY is used.
Not a part of SELECT ... INTO - If we are just beginning work with enterprise class databases it is easy to forget that, unlike consumer class personal file managers, records within tables in high end databases should never be assumed to be stored in some particular order. If we want to see records in some particular order we always must do so using an ORDER BY clause.
A consequence of records being stored in tables in no particular order is that there is no role for ORDER BY in a SELECT ... INTO clause. Suppose, for example, we have a table of Customers with a Name field. For convenience, we would that table to always open by default with a particular order, so we attempt the following incorrect query:
SELECT * INTO [Customers by Name] FROM [Customers] ORDER BY [NAME];
That will not work because ORDER BY has no role in how records are stored in tables. If we want to see the Customers table ordered by Name, we could create a query called [Customers by Name] that contained the SQL:
SELECT * FROM [Customers] ORDER BY [NAME];
We can then use the query wherever we want to see a sorted table.
Discussion: Why are records stored in tables without a specific sort order? Primarily for performance and flexibility when working with larger or more complex data sets. Storing records in sort order means constantly updating a database whenever records are edited, inserted or deleted, which very rapidly becomes expensive and inconvenient. Changing just the first letter of a record means moving it in sort order if the table stores records in sort order, which can involve a lot of work in a very large table and, while the table is being reorganized, introduce complications and delays for other processes and people that may want to simultaneously access that table.
There are always technical means that, at a cost, can resolve such complexities. For simple, small databases used by only one person in a non-parallel way such means are not particularly costly or complex. But for databases that are designed for more sophisticated applications it turns out that a more effective approach is to not store records in sort order but instead to store records in whatever storage order is most effective for the internal machinery and then to always generate a desired, sorted view of the data on the fly using ORDER BY.
Ambiguous forms not supported - Some traditional, but ambiguous forms of ORDER BY used in various databases are not allowed by Manifold. For example,
SELECT * FROM t ORDER BY 1;
...is an acceptable construction in some systems meaning to take all data from table t and to sort it by the first field. However, that syntax is not supported by Manifold since it is ambiguous given the evaluation of expressions in the form of:
SELECT * FROM t ORDER BY 1+1;
The ambiguity arises because the "order by 1" case sorts by a field while the "order by 1+1" case would sort by a constant expression, that is, wouldn't sort at all.