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.

This page covers the following topics:

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.CriteriaQuery - JPA InterfaceThe 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:

  CriteriaQueryjavax.persistence.criteria.CriteriaQuery - JPA InterfaceThe CriteriaQuery interface defines functionality that is specific
 to top-level queries.<Country> q = cb.createQueryCriteriaBuilder.createQuery(resultClass) - JPA MethodCreate a CriteriaQuery object with the specified result
  type.(Country.class);
  Rootjavax.persistence.criteria.Root - JPA InterfaceA root type in the from clause.<Country> c = q.fromAbstractQuery.from(entityClass) - JPA MethodCreate and add a query root corresponding to the given entity,
 forming a cartesian product with any existing roots.(Country.class);
  q.selectCriteriaQuery.select(selection) - JPA MethodSpecify the item that is to be returned in the query result.(c);
  q.orderByCriteriaQuery.orderBy(o) - JPA MethodSpecify the ordering expressions that are used to
 order the query results.(cb.ascCriteriaBuilder.asc(x) - JPA MethodCreate an ordering by the ascending value of the expression.(c.getPath.get(attributeName) - JPA MethodCreate a path corresponding to the referenced attribute.("currency")), cb.descCriteriaBuilder.desc(x) - JPA MethodCreate an ordering by the descending value of the expression.(c.getPath.get(attributeName) - JPA MethodCreate a path corresponding to the referenced attribute.("population")));

Unlike other methods for setting criteria query clauses - the orderByCriteriaQuery.orderBy(o) - JPA MethodSpecify the ordering expressions that are used to order the query results. method takes a variable number of Orderjavax.persistence.criteria.Order - JPA InterfaceAn object that defines an ordering over the query results. instances as arguments (or a listCriteriaQuery.orderBy(o) - JPA MethodSpecify the ordering expressions that are used to order the query results. of Order) rather than Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions. instances.

The Orderjavax.persistence.criteria.Order - JPA InterfaceAn object that defines an ordering over the query results. interface is merely a thin wrapper around Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions., which adds order direction - either ascending (ASC) or descending (DESC). The CriteriaBuilder's ascCriteriaBuilder.asc(x) - JPA MethodCreate an ordering by the ascending value of the expression. and descCriteriaBuilder.desc(x) - JPA MethodCreate an ordering by the descending value of the expression. methods (which are demonstrated above) take an expression and return an ascending or descending Orderjavax.persistence.criteria.Order - JPA InterfaceAn object that defines an ordering over the query results. instance (respectively).

< JPQL GROUP BY ^ Query Structure DELETE Queries >