SQL 101 5: ORDER and GROUP

This is the fifth in my series of posts designed to teach people the basics of SQL and it’s use in interrogating databases.

The Optional ORDER Clause
The ORDER clause provides an optional method of sorting the rows that have been returned to the Results Table. Please Note that using a sort can sometimes take longer for the query to execute, and require more memory on the Server. An example of the ORDER clause is as follows:

ORDER BY CUS_NAME, CUS_PRIMARYKEY

The order of sorting is determined by the order of the Columns within the ORDER section of the statement. The sort is carried out in ascending order of field value.

Below are some examples of queries with the optional ORDER clause in use.

SELECT SOH_NUMBER, SOH_DATE, SOH_NET, SOH_OWNER
FROM GUSER.F_SOHDR
WHERE (SOH_OWNER = 'JOHN CLARK' OR SOH_OWNER = 'PETER OAKDEN') AND SOH_NET > 3000
ORDER BY SOH_NET

SELECT SOH_NUMBER, SOH_DATE, SOH_NET, SOH_OWNER
FROM GUSER.F_SOHDR
WHERE (SOH_OWNER = 'JOHN CLARK' OR SOH_OWNER = 'PETER OAKDEN') AND SOH_NET > 3000
ORDER BY SOH_NET DESC


The Optional GROUP Clause
The GROUP clause is used to sort the output not as individual records, but as groups of records that have the same value in a specified field. This optional clause is only used when the query is applying one of the functions (AVERAGE, SUM, MIN, MAX or COUNT) to the groups.

The SELECT clause is limited to the Column that appears in the GROUP clause, with one or more functions of other Columns. For Example:

SELECT HIS_USER, COUNT(*)
FROM GUSER.F_SYSHISTORY
WHERE HIS_USER LIKE ‘%’
GROUP BY HIS_USER

This query returns a row containing the amount of system history records against each user.

Next: DISTINCT Option, Calculated Columns and Functions

"Don't join the book burners. Don't think you're going to conceal faults by concealing evidence that they ever existed. Don't be afraid to go in your library and read every book..." - Dwight D Eisenhower