2.10.2.9. JPQL ORDER BY Clause
The ORDER BY clause allows the objects or values that are returned by the query to be ordered.
The syntax of the ORDER BY clause is
-
orderby_clause ::= ORDER BY orderby_item {, orderby_item}*
-
orderby_item ::= { state_field_path_expression | result_variable } [ASC | DESC]
An orderby_item must be one of the following:
-
A
state_field_path_expressionthat evaluates to an orderable state field of an entity or embeddable class abstract schema type designated in the SELECT clause by one of the following:-
a
general_identification_variable -
a
single_valued_object_path_expression
-
-
A
state_field_path_expressionthat evaluates to the same state field of the same entity or embeddable abstract schema type as astate_field_path_expressionin theSELECTclause. -
A
result_variablethat refers to an orderable item in theSELECTclause for which the sameresult_variablehas been specified. This may be the result of anaggregate_expression, ascalar_expression, or astate_field_path_expressionin theSELECTclause.
For example, the five queries below are legal.
SELECT pub FROM Publisher pub ORDER BY pub.revenue, pub.name
SELECT o
FROM Customer c JOIN c.orders o JOIN c.address a
WHERE a.state = ‘CA’
ORDER BY o.quantity DESC, o.totalcost
SELECT o.quantity, a.zipcode
FROM Customer c JOIN c.orders o JOIN c.address a
WHERE a.state = ‘CA’
ORDER BY o.quantity, a.zipcode
SELECT o.quantity, o.cost*1.08 AS taxedCost, a.zipcode
FROM Customer c JOIN c.orders o JOIN c.address a
WHERE a.state = ‘CA’ AND a.county = ‘Santa Clara’
ORDER BY o.quantity, taxedCost, a.zipcode
SELECT AVG(o.quantity) as q, a.zipcode
FROM Customer c JOIN c.orders o JOIN c.address a
WHERE a.state = ‘CA’
GROUP BY a.zipcode
ORDER BY q DESC
The following two queries are not legal because the orderby_item is not reflected in the SELECT clause of the query.
SELECT p.product_name
FROM Order o JOIN o.lineItems l JOIN l.product p JOIN o.customer c
WHERE c.lastname = ‘Smith’ AND c.firstname = ‘John’
ORDER BY p.price
SELECT p.product_name
FROM Order o, IN(o.lineItems) l JOIN o.customer c
WHERE c.lastname = ‘Smith’ AND c.firstname = ‘John’
ORDER BY o.quantity
If more than one orderby_item is specified, the left-to-right sequence of the orderby_item elements determines the precedence, whereby the leftmost orderby_item has highest precedence.
The keyword ASC specifies that ascending ordering be used; the keyword DESC specifies that descending ordering be used.
Ascending ordering is the default.
SQL rules for the ordering of null values apply: that is, all null values must appear before all non-null values in the ordering or all null values must appear after all non-null values in the ordering, but it is not specified which.
The ordering of the query result is preserved in the result of the query method if the ORDER BY clause is used.