ORDER BY clause (JPQL / Criteria API)

The ORDER BY clause specifies a required order for the query results. Any JPQL query that does not include an ORDER BY clause produces results in an undefined and non-deterministic order.

ORDER BY Expressions

The following query returns names of countries whose population size is at least one million people, ordered by the country name:

SELECT c.name FROM Country c WHERE c.population > 1000000 ORDER BY c.name

When an ORDER BY clause exists it is the last to be executed. First the FROM clause produces objects for examination and the WHERE clause selects which objects to collect as results. Then the SELECT clause builds the results by evaluating the result expressions. Finally the results are ordered by evaluation of the ORDER BY expressions.

Only expressions that are derived directly from expressions in the SELECT clause are allowed in the ORDER BY clause. The following query, for example, is invalid because the ORDER BY expression is not part of the results:

SELECT c.name
FROM Country c
WHERE c.population > 1000000
ORDER BY c.population

On the other hand, the following query is valid because, given a Country c, the c.population expression can be evaluated from c:

SELECT c
FROM Country c
WHERE c.population > 1000000
ORDER BY c.population

When using ObjectDB, any JPQL expression whose type is comparable (i.e. numbers, strings and date values) and is derived from the SELECT expressions can be used in the ORDER BY clause. Some JPA implementations are more restrictive. Path expressions are supported by all the JPA implementations but support for other JPQL expressions is vendor dependent.

Query results can also be ordered by multiple order expressions. In this case, the first expression is the primary order expression. Any additional order expression is used to order results for which all the previous order expressions produce the same values.

The following query returns Country objects ordered by currency as the primary sort key and by name as the secondary sort key:

SELECT c.currency, c.name
FROM Country c
ORDER BY c.currency, c.name

To avoid repeating result expressions in the ORDER BY JPQL supports defining aliases for SELECT expressions and then using the aliases in the ORDER BY clause. The following query is equivalent to the query above:

SELECT c.currency AS currency, c.name AS name
FROM Country c
ORDER BY currency, name

Alias variables are referred to as result variables to distinguish them from the identification variables that are defined in the FROM clause.

Order Direction (ASC, DESC)

The default ordering direction is ascending. Therefore, when ascending order is required it is usually omitted even though it could be specified explicitly, as follows:

SELECT c.name FROM Country c ORDER BY c.name ASC

On the other hand, to apply descending order the DESC keyword must be added explicitly to the order expression:

SELECT c.name FROM Country c ORDER BY c.name DESC

Grouping (GROUP BY) Order

The ORDER BY clause is always the last in the query processing chain. If a query contains both an ORDER BY clause and a GROUP BY clause the SELECT clause receives groups rather than individual objects and ORDER BY can order these groups. For example:

SELECT c.currency, SUM(c.population)
FROM Country c
WHERE 'Europe' MEMBER OF c.continents
GROUP BY c.currency
HAVING COUNT(c) > 1
ORDER BY c.currency

The ORDER BY clause in the above query orders the results by the currency name. Without an ORDER BY clause the result order would be undefined.

ORDER BY in Criteria Queries

The CriteriaQueryjavax.persistence.criteria.CriteriaQueryJPA interfaceThe CriteriaQuery interface defines functionality that is specific to top-level queries.See JavaDoc Reference Page... interface provides methods for setting the ORDER BY clause.

For example, the following JPQL query:

SELECT c
FROM Country c
ORDER BY c.currency, c.population DESC

can be built using the criteria query API as follows:

  CriteriaQueryjavax.persistence.criteria.CriteriaQueryJPA interfaceThe CriteriaQuery interface defines functionality that is specific 
 to top-level queries.See JavaDoc Reference Page...<Country> q = cb.createQuerycreateQuery(resultClass)CriteriaBuilder's methodCreate a CriteriaQuery object with the specified result 
  type.See JavaDoc Reference Page...(Country.class);
  Rootjavax.persistence.criteria.RootJPA interfaceA root type in the from clause.See JavaDoc Reference Page...<Country> c = q.fromfrom(entityClass)AbstractQuery's methodCreate and add a query root corresponding to the given entity,
 forming a cartesian product with any existing roots.See JavaDoc Reference Page...(Country.class);
  q.selectselect(selection)CriteriaQuery's methodSpecify the item that is to be returned in the query result.See JavaDoc Reference Page...(c);
  q.orderByorderBy(o)CriteriaQuery's methodSpecify the ordering expressions that are used to
 order the query results.See JavaDoc Reference Page...(cb.ascasc(x)CriteriaBuilder's methodCreate an ordering by the ascending value of the expression.See JavaDoc Reference Page...(c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("currency")), cb.descdesc(x)CriteriaBuilder's methodCreate an ordering by the descending value of the expression.See JavaDoc Reference Page...(c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("population")));

Unlike other methods for setting criteria query clauses - the orderByorderBy(o)CriteriaQuery's methodSpecify the ordering expressions that are used to order the query results.See JavaDoc Reference Page... method takes a variable number of Orderjavax.persistence.criteria.OrderJPA interfaceAn object that defines an ordering over the query results.See JavaDoc Reference Page... instances as arguments (or a listorderBy(o)CriteriaQuery's methodSpecify the ordering expressions that are used to order the query results.See JavaDoc Reference Page... of Order) rather than Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page... instances.

The Orderjavax.persistence.criteria.OrderJPA interfaceAn object that defines an ordering over the query results.See JavaDoc Reference Page... interface is merely a thin wrapper around Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page..., which adds order direction - either ascending (ASC) or descending (DESC). The CriteriaBuilder's ascasc(x)CriteriaBuilder's methodCreate an ordering by the ascending value of the expression.See JavaDoc Reference Page... and descdesc(x)CriteriaBuilder's methodCreate an ordering by the descending value of the expression.See JavaDoc Reference Page... methods (which are demonstrated above) take an expression and return an ascending or descending Orderjavax.persistence.criteria.OrderJPA interfaceAn object that defines an ordering over the query results.See JavaDoc Reference Page... instance (respectively).