Databases Reference
In-Depth Information
If you change column order in the ORDER BY clause, the result also changes
significantly. For example, the query:
SELECT * FROM SALES
ORDER BY SPNUM, PRODNUM
gives you the following result:
SPNUM PRODNUM QUANTITY
137 19440 473
137 24013 170
137 26722 688
186 16386 1745
186 19440 2529
186 21765 1962
186 24013 3071
204 21765 809
204 26722 734
361 16386 3729
361 21765 3110
361 26722 2738
The query still returns the same rows, but returns them in a different order.
It's important to note that the column or columns used in the ORDER BY
list don't necessarily have to be part of the SELECT clause column list. For exam-
ple, the following query would run without generating an error:
SELECT PRODNUM, PRODUCTNAME FROM PRODUCT
WHERE QOO =0 ORDER BY QOH
Let's break down exactly what this is requesting. The query should return the
PRODNUM and PRODUCTNAME columns, limited to those columns in which
the QOO (quantity on order) column has a value of 0. Sort the result in ascend-
ing order by the value in the QOH (quantity on hand) column. This gives you
the following result:
PRODNUM PRODUCTNAME
26722 Pliers
21765 Drill
24013 Saw
19441 Hammer
16386 Wrench
Notice in the SELECT statement that neither QOO or QOH is included in the
SELECT column list.
Organizing Data with GROUP BY
In addition to ordering the result, you can also group the rows in the result.
Many situations require calculations on several different groups of rows using
