Java Reference
In-Depth Information
SELECT *
FROM CUSTOMERS
WHERE Last_Name = 'Corleone'
ORDER BY First_Name DESC;
Sorting on multiple columns is also easy to do by using a sort list. For example, to sort
the data in ascending order based on Last_Name and then sort duplicates using the
First_Name in descending order, the SQL statement is as follows:
SELECT First_Name, MI, Last_Name, Street, City, State, Zip
FROM CUSTOMERS
ORDER BY Last_Name, First_Name DESC;
When no ORDER BY clause is used, the order of the output of a query is
undefined.
Note
The rules for using ORDER BY are as follows:
The ORDER BY clause must be the last clause in the SELECT statement.
Default sort order is ascending.
You can specify ascending order with the keyword ASC.
You can specify descending order with the keyword DESC.
You can use column names or expressions in the ORDER BY clause.
The column names in the ORDER BY clause do not have to be specified in the select list.
NULLS usually occur first in the sort order.
Summarizing the Results of a Query
Another common reporting requirement is to break down the data a query returns into
various groups so that it can be summarized in some way. The GROUP BY clause
enables you to combine database records to perform calculations such as averages
or counts on groups of records.
The GROUP BY clause combines records with identical values in a specified field into
a single record for this purpose, as shown in the following example:
SELECT Description, COUNT(Description) AS 'Count', AVG(Cost) AS 'Average
Cost'
FROM Inventory
GROUP BY Description;
The results of this query will be as follows:
Description
Count
Average Cost
Cereal
4
1.745
