Clauses, Sorting, and SQL Queries - The order by Clause
(Page 2 of 5 )
In general, the rows in a result set returned from a query are not in any particular order. If you want to your result set in a particular order, you will need to instruct the server to sort the results using the order by clause:
Theorder byclause is the mechanism for sorting your result set using either raw column data or expressions based on column data.
For example, here’s another look at an earlier query against theaccount table:
mysql> SELECT open_emp_id, product_cd -> FROM account;
+-------------+------------+ | open_emp_id | product_cd | +-------------+------------+ | 10 | CHK | | 10 | SAV | | 10 | CD | | 10 | CHK | | 10 | SAV | | 13 | CHK | | 13 | MM | | 1 | CHK | | 1 | SAV | | 1 | MM | | 16 | CHK | | 1 | CHK | | 1 | CD | | 10 | CD | | 16 | CHK | | 16 | SAV | | 1 | CHK | | 1 | MM | | 1 | CD | | 16 | CHK | | 16 | BUS | | 10 | BUS | | 16 | CHK | | 13 | SBL | +-------------+------------+ 24 rows in set (0.00 sec)
If you are trying to analyze data for each employee, it would be helpful to sort the results by theopen_emp_idcolumn; to do so, simply add this column to theorder byclause:
mysql> SELECT open_emp_id, product_cd -> FROM account -> ORDER BY open_emp_id;
+-------------+------------+ | open_emp_id | product_cd | +-------------+------------+ | 1 | CHK | | 1 | SAV | | 1 | MM | | 1 | CHK | | 1 | CD | | 1 | CHK | | 1 | MM | | 1 | CD | | 10 | CHK | | 10 | SAV | | 10 | CD | | 10 | CHK | | 10 | SAV | | 10 | CD | | 10 | BUS | | 13 | CHK | | 13 | MM | | 13 | SBL | | 16 | CHK | | 16 | CHK | | 16 | SAV | | 16 | CHK | | 16 | BUS | | 16 | CHK | +-------------+------------+ 24 rows in set (0.00 sec)
It is now easier to see what types of accounts were opened by each employee. However, it might be even better if you could ensure that the account types were shown in the same order for each distinct employee; this can be accomplished by adding theproduct_cdcolumn after theopen_emp_idcolumn in theorder byclause:
mysql> SELECT open_emp_id, product_cd -> FROM account -> ORDER BY open_emp_id, product_cd;
+-------------+------------+ | open_emp_id | product_cd | +-------------+------------+ | 1 | CD | | 1 | CD | | 1 | CHK | | 1 | CHK | | 1 | CHK | | 1 | MM | | 1 | MM | | 1 | SAV | | 10 | BUS | | 10 | CD | | 10 | CD | | 10 | CHK | | 10 | CHK | | 10 | SAV | | 10 | SAV | | 13 | CHK | | 13 | MM | | 13 | SBL | | 16 | BUS | | 16 | CHK | | 16 | CHK | | 16 | CHK | | 16 | CHK | | 16 | SAV | +-------------+------------+ 24 rows in set (0.00 sec)
The result set has now been sorted first by employee ID and then secondly by the account type. The order that columns appear in yourorder byclause does make a difference.
/ 5 