ORDER BY clause (JPQL / Criteria API)

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

ORDER BY expressions

The following query returns the names of countries with a population of at least one million, ordered by country name:

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

If a query includes an ORDER BY clause, it is the last clause to be executed. The FROM clause first produces objects for examination, and the WHERE clause filters them. Then, the SELECT clause builds the results by evaluating the result expressions. Finally, the ORDER BY clause sorts the results by evaluating the order expressions.

The ORDER BY clause can only contain expressions that are derived directly from expressions in the SELECT clause. For example, the following query is invalid because the ORDER BY expression, c.population, is not part of the SELECT clause:

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

However, the following query is valid because the ORDER BY expression c.population can be derived from c, which is returned by the SELECT clause:

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

When using ObjectDB, any JPQL expression whose type is comparable (for example, 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 JPA implementations, but support for other JPQL expressions is vendor-dependent.

Query results can also be sorted by multiple expressions. The first expression is the primary sort key. Additional expressions act as secondary sort keys, used to order results for which the preceding expressions produce identical 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 clause, JPQL supports defining aliases for SELECT expressions and then using the aliases in the ORDER BY clause. The following query is equivalent to the one above:

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

These aliases are called result variables to distinguish them from identification variables, which are defined in the FROM clause.

Order direction (ASC, DESC)

The default sort direction is ascending (ASC). Therefore, the ASC keyword is optional and usually omitted even though it could be specified explicitly, as follows:

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

However, to sort in descending order, you must explicitly add the DESC keyword 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 clause in the query processing chain. If a query contains both a GROUP BY clause and an ORDER BY clause, the GROUP BY clause first aggregates the results into groups. The ORDER BY clause then sorts 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

In the query above, the ORDER BY clause sorts the results by currency name. Without an ORDER BY clause, the result order would be undefined.

ORDER BY in criteria queries

The CriteriaQueryjakarta.persistence.criteria.CriteriaQueryThe CriteriaQuery interface defines functionality that is specific to top-level queries. 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:

  CriteriaQueryjakarta.persistence.criteria.CriteriaQueryThe  CriteriaQuery  interface defines functionality that is specific to top-level queries.<Country> q = cb.createQueryjakarta.persistence.criteria.CriteriaBuilder.createQuery(Class)Create a   CriteriaQuery<T>   object with the given result type.(Country.class);
  Rootjakarta.persistence.criteria.RootA root type in the from clause.<Country> c = q.fromjakarta.persistence.criteria.AbstractQuery.from(Class)Create and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.(Country.class);
  q.selectjakarta.persistence.criteria.CriteriaQuery.select(Selection)Specify the item that is to be returned in the query result.(c);
  q.orderByjakarta.persistence.criteria.CriteriaQuery.orderBy(Order...)Specify the ordering expressions that are used to order the query results.(cb.ascjakarta.persistence.criteria.CriteriaBuilder.asc(Expression)Create an ordering by the ascending value of the expression.(c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("currency")), cb.descjakarta.persistence.criteria.CriteriaBuilder.desc(Expression)Create an ordering by the descending value of the expression.(c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("population")));

Unlike other methods for setting criteria query clauses, the orderByjakarta.persistence.criteria.CriteriaQuery.orderBy(Order...)Specify the ordering expressions that are used to order the query results. method accepts a variable number of Orderjakarta.persistence.criteria.OrderAn object that defines an ordering over the query results. arguments (or a Listjakarta.persistence.criteria.CriteriaQuery.orderBy(List)Specify the ordering expressions that are used to order the query results. of Order instances) rather than Expressionjakarta.persistence.criteria.ExpressionType for query expressions. instances.

The Orderjakarta.persistence.criteria.OrderAn object that defines an ordering over the query results. interface is a thin wrapper around Expressionjakarta.persistence.criteria.ExpressionType for query expressions. that adds a sort direction: ascending (ASC) or descending (DESC). As shown in the example, the CriteriaBuilder interface provides ascjakarta.persistence.criteria.CriteriaBuilder.asc(Expression)Create an ordering by the ascending value of the expression. and descjakarta.persistence.criteria.CriteriaBuilder.desc(Expression)Create an ordering by the descending value of the expression. methods. These methods take an Expression and return an Order instance with an ascending or descending direction, respectively.