background image

The DISTINCT Keyword

<< Aggregate Functions in the SELECT Clause | The GROUP BY Clause >>
<< Aggregate Functions in the SELECT Clause | The GROUP BY Clause >>

The DISTINCT Keyword

SELECT SUM(l.price)
FROM Order o JOIN o.lineItems l JOIN o.customer c
WHERE c.lastname =
'Coss' AND c.firstname = 'Roxane'
The following example returns the total number of orders:
SELECT COUNT(o)
FROM Order o
The following example returns the total number of items in Hal Incandenza's order that have
prices:
SELECT COUNT(l.price)
FROM Order o JOIN o.lineItems l JOIN o.customer c
WHERE c.lastname =
'Incandenza' AND c.firstname = 'Hal'
The DISTINCT Keyword
The DISTINCT keyword eliminates duplicate return values. If a query returns a
java.util.Collection
, which allows duplicates, then you must specify the DISTINCT keyword
to eliminate duplicates.
Constructor Expressions
Constructor expressions allow you to return Java instances that store a query result element
instead of an Object[].
The following query creates a CustomerDetail instance per Customer matching the WHERE
clause. A CustomerDetail stores the customer name and customer's country name. So the
query returns a List of CustomerDetail instances:
SELECT NEW com.xyz.CustomerDetail(c.name, c.country.name)
FROM customer c
WHERE c.lastname =
'Coss' AND c.firstname = 'Roxane'
ORDER BY
Clause
As its name suggests, the ORDER BY clause orders the values or objects returned by the query.
If the ORDER BY clause contains multiple elements, the left-to-right sequence of the elements
determines the high-to-low precedence.
The ASC keyword specifies ascending order (the default), and the DESC keyword indicates
descending order.
Full Query Language Syntax
Chapter 27 · The Java Persistence Query Language
761