| Team Fly | |
So far, all of our select queries have returned records in random order. Earlier, we selected records from the customer table where the customer was located in either Connecticut or Utah and had a credit limit of $15,000. The results came back in no apparent order. It is often desirable to order the result set on one or more of the selected columns. In this case, it probably would have been easier to interpret the results if they were sorted by state, and within that state were then sorted by customer ID. Let's take a look at the query syntax and resulting output:
SQL> select cust_id, cust_state_province, cust_credit_limit
2 from customers
3 where cust_credit_limit = 15000
4 and cust_state_province in ('UT','CT')
5 order by cust_state_province, cust_id;
CUST_ID CUST_STATE_PROVINCE CUST_CREDIT_LIMIT
---------- ---------------------------------------- -----------------
101515 CT 15000
101798 CT 15000
102238 CT 15000
102579 CT 15000
103171 CT 15000
104381 CT 15000
24830 UT 15000
103007 UT 15000
8 rows selected.
Any column specified in the order by statement could either be sorted in ascending or descending order. By default, Oracle will sort each column in ascending order. In order to sort a column in descending order, the use of desc following the order by column will accomplish this. Let's look at the previous example one more time with the customer IDs sorted in descending order:
SQL> select cust_id, cust_state_province, cust_credit_limit
2 from customers
3 where cust_credit_limit = 15000
4 and cust_state_province in ('UT','CT')
5 order by cust_state_province, cust_id desc;
CUST_ID CUST_STATE_PROVINCE CUST_CREDIT_LIMIT
---------- ---------------------------------------- -----------------
104381 CT 15000
103171 CT 15000
102579 CT 15000
| Team Fly | |