Select specific columns

The previous section shows how to select and order all data from a table. However, often all you want to see is the data in one or more specific columns. Again, the formula is to use the Projection and FROM clauses, specify the columns and table, and perhaps order the data in ascending or descending order with an ORDER BY clause.

If you want to find all the customer numbers in the orders table, use a statement such as the one in the following query.

Figure 1: Query

SELECT customer_num FROM orders;

The result shows how the statement simply selects all data in the customer_num column in the orders table and lists the customer numbers on all the orders, including duplicates.

Figure 2: Query result

customer_num 

         104
         101
         104
         ⋮
         122
         123
         124
         126
         127

The output includes several duplicates because some customers have placed more than one order. Sometimes you want to see duplicate rows in a projection. At other times, you want to see only the distinct values, not how often each value appears.

To suppress duplicate rows, you can include the keyword DISTINCT or its synonym UNIQUE at the start of the select list, once in each level of a query, as the following query shows.

Figure 3: Query

SELECT DISTINCT customer_num FROM orders;

SELECT UNIQUE customer_num FROM orders;

To produce a more readable list, Query limits the display to show each customer number in the orders table only once, as the result shows.

Figure 4: Query result

customer_num 

         101
         104
         106
         110
         111
         112
         115
         116
         117
         119
         120
         121
         122
         123
         124
         126
         127

Suppose you are handling a customer call, and you want to locate purchase order number DM354331. To list all the purchase order numbers in the orders table, use a statement such as the following query shows.

Figure 5: Query

SELECT po_num FROM orders;

The result shows how the statement retrieves data in the po_num column in the orders table.

Figure 6: Query result

However, the list is not in a useful order. You can add an ORDER BY clause to sort the column data in ascending order and make it easier to find that particular po_num, as shown in the following query.

Figure 7: Query

SELECT po_num FROM orders ORDER BY po_num;

Figure 8: Query result

To select multiple columns from a table, list them in the projection list in the Projection clause. The following query shows that the order in which the columns are selected is the order in which they are retrieved, from left to right.

Figure 9: Query

SELECT ship_date, order_date, customer_num, 
       order_num, po_num
   FROM orders
   ORDER BY order_date, ship_date;

As Sorting on multiple columns shows, you can use the ORDER BY clause to sort the data in ascending or descending order and perform nested sorts. The result shows ascending order.

Figure 10: Query result

ship_date  order_date customer_num   order_num po_num
       
06/01/1998 05/20/1998          104        1001 B77836
05/26/1998 05/21/1998          101        1002 9270
05/23/1998 05/22/1998          104        1003 B77890
05/30/1998 05/22/1998          106        1004 8006
06/09/1998 05/24/1998          116        1005 2865
           05/30/1998          112        1006 Q13557
06/05/1998 05/31/1998          117        1007 278693
07/06/1998 06/07/1998          110        1008 LZ230
06/21/1998 06/14/1998          111        1009 4745
06/29/1998 06/17/1998          115        1010 429Q
06/29/1998 06/18/1998          117        1012 278701
07/03/1998 06/18/1998          104        1011 B77897
07/10/1998 06/22/1998          104        1013 B77930
07/03/1998 06/25/1998          106        1014 8052
07/16/1998 06/27/1998          110        1015 MA003
07/12/1998 06/29/1998          119        1016 PC6782
07/13/1998 07/09/1998          120        1017 DM354331
07/13/1998 07/10/1998          121        1018 S22942
07/16/1998 07/11/1998          122        1019 Z55709
07/16/1998 07/11/1998          123        1020 W2286
07/25/1998 07/23/1998          124        1021 C3288
07/30/1998 07/24/1998          126        1022 W9925
07/30/1998 07/24/1998          127        1023 KF2961

When you use SELECT and ORDER BY on several columns in a table, you might find it helpful to use integers to refer to the position of the columns in the ORDER BY clause. When an integer is an element in the ORDER BY list, the database server treats it as the position in the projection list. For example, using 3 in the ORDER BY list (ORDER BY 3) refers to the third item in the projection list. The statements in the following query retrieve and display the same data, as Query result shows.

Figure 11: Query

SELECT customer_num, order_num, po_num, order_date
   FROM orders
   ORDER BY 4, 1;

SELECT customer_num, order_num, po_num, order_date
   FROM orders
   ORDER BY order_date, customer_num;

Figure 12: Query result

customer_num   order_num po_num     order_date 

         104        1001 B77836     05/20/1998
         101        1002 9270       05/21/1998
         104        1003 B77890     05/22/1998
         106        1004 8006       05/22/1998
         116        1005 2865       05/24/1998
         112        1006 Q13557     05/30/1998
         117        1007 278693     05/31/1998
         110        1008 LZ230      06/07/1998
         111        1009 4745       06/14/1998
         115        1010 429Q       06/17/1998
         104        1011 B77897     06/18/1998
         117        1012 278701     06/18/1998
         104        1013 B77930     06/22/1998
         106        1014 8052       06/25/1998
         110        1015 MA003      06/27/1998
         119        1016 PC6782     06/29/1998
         120        1017 DM354331   07/09/1998
         121        1018 S22942     07/10/1998
         122        1019 Z55709     07/11/1998
         123        1020 W2286      07/11/1998
         124        1021 C3288      07/23/1998
         126        1022 W9925      07/24/1998
         127        1023 KF2961     07/24/1998

You can include the DESC keyword in the ORDER BY clause when you assign integers to column names, as the following query shows.

Figure 13: Query

SELECT customer_num, order_num, po_num, order_date
   FROM orders
   ORDER BY 4 DESC, 1;

In this case, data is first sorted in descending order by order_date and in ascending order by customer_num.