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 Figure 28.
SELECT customer_num FROM orders
Figure 29 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.
customer_num
104
101
104
·
·
·
122
123
124
126
127The 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 Figure 30 shows.
SELECT DISTINCT customer_num FROM orders SELECT UNIQUE customer_num FROM orders
To produce a more readable list, Figure 30 limits the display to show each customer number in the orders table only once, as Figure 31 shows.
customer_num
101
104
106
110
111
112
115
116
117
119
120
121
122
123
124
126
127Suppose 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 one that Figure 32 shows.
SELECT po_num FROM orders
Figure 33 shows how the statement retrieves data in the po_num column in the orders table.
po_num B77836 9270 B77890 8006 2865 Q13557 278693
·
·
·
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 Figure 35 shows.
SELECT po_num FROM orders ORDER BY po_num
po_num 278693 278701 2865 429Q 4745 8006 8052 9270 B77836 B77890
·
·
·
To select multiple columns from a table, list them in the projection list in the Projection clause. Figure 36 shows that the order in which the columns are selected is the order in which they are retrieved, from left to right.
SELECT ship_date, order_date, customer_num,
order_num, po_num
FROM orders
ORDER BY order_date, ship_dateAs 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. Figure 37 shows ascending order.
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 KF2961When 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 Figure 38 retrieve and display the same data, as Figure 39 shows.
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
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/1998You can include the DESC keyword in the ORDER BY clause when you assign integers to column names, as Figure 40 shows.
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.
To select part of the value of a character column, include a substring in the projection list. Suppose your marketing department is planning a mailing to your customers and wants their geographical distribution based on zip codes. You could write a query similar to the one that Figure 41 shows.
SELECT zipcode[1,3], customer_num FROM customer ORDER BY zipcode
Figure 41 uses a substring to select the first three characters of the zipcode column (which identify the state) and the full customer_num, and lists them in ascending order by zip code, as Figure 42 shows.
zipcode customer_num 021 125 080 119 085 122 198 121 322 123
·
·
·
943 103 943 107 946 118
By default, Informix database servers use the U.S. English language environment, called a locale, for database data. The U.S. English locale specifies data sorted in code-set order. This default locale uses the ISO 8859-1 code set.
If your database contains non-English data, you should store non-English data in NCHAR (or NVARCHAR) columns to obtain results sorted by the language. The ORDER BY clause should return data in the order appropriate to that language. Figure 43 uses a SELECT statement with an ORDER BY clause to search the table, abonnés, and to order the selected information by the data in the nom column.
SELECT numéro,nom,prénom FROM abonnés ORDER BY nom
The collation order for the results of this query can vary, depending on the following system variations:
For Figure 43 to return expected results, the nom column should be NCHAR data type in a database that uses a French locale. Other operations, such as less than, greater than, or equal to, are also affected by the user-specified locale. For more information on non-English data and locales, see the IBM Informix: GLS User's Guide.
Figure 44 and Figure 45 show two sample sets of output.
numéro nom prénom 13612 Azevedo Edouardo Freire 13606 Dupré Michèle Françoise 13607 Hammer Gerhard 13602 Hämmer le Greta 13604 LaForêt Jean-Noël 13610 LeMaître Héloïse 13613 Llanero Gloria Dolores 13603 Montaña José Antonio 13611 Oatfield Emily 13609 Tiramisù Paolo Alfredo 13600 da Sousa João Lourenço Antunes 13615 di Girolamo Giuseppe 13601 Ålesund Sverre 13608 Étaix Émile 13605 Ötker Hans-Jürgen 13614 Øverst Per-Anders
Figure 44 follows the ISO 8859-1 code-set order, which ranks uppercase letters before lowercase letters and moves names that contain an accented character (Ålesund, Étaix, Ötker, and Øverst) to the end of the list.
numéro nom prénom 13601 Ålesund Sverre 13612 Azevedo Edouardo Freire 13600 da Sousa João Lourenço Antunes 13615 di Girolamo Giuseppe 13606 Dupré Michèle Françoise 13608 Étaix Émile 13607 Hammer Gerhard 13602 Hämmer le Greta 13604 LaForêt Jean-Noël 13610 LeMaître Héloïse 13613 Llanero Gloria Dolores 13603 Montaña José Antonio 13611 Oatfield Emily 13605 Ötker Hans-Jürgen 13614 Øverst Per-Anders 13609 Tiramisù Paolo Alfredo
Figure 45 shows that when the appropriate locale file is referenced by the database server, names including non-English characters (Ålesund, Étaix, Ötker, and Øverst) are collated differently than they are in the ISO 8859-1 code set. They are sorted correctly for the locale. It does not distinguish between uppercase and lowercase letters.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]