Theprevioussectionshowshowto selectandorderall datafromatable. However,oftenallyouwanttoseeisthedatainoneormore specific columns.Again, theformulaistousetheProjection andFROMclauses, specifythecolumns andtable,andperhaps orderthedatainascendingor descendingorderwith anORDERBYclause.
Ifyouwanttofindallthecustomernumbersintheorderstable,usea statementsuchastheoneinFigure2-24.
stock_num manu_code description unit_price
302 HRO ice pack $4.50 302 KAR ice pack $5.00 5 ANZ tennis racquet $19.80 9 ANZ volleyball net $20.00 103 PRC frnt derailleur $20.00 . . . 108 SHM crankset $45.00 6 ANZ tennis ball $48.00 305 HRO first-aid kit $48.00 303 PRC socks $48.00 311 SHM water gloves $48.00 . . . 113 SHM 18-spd, assmbld $685.90 1 HSK baseball gloves $800.00 8 ANZ volleyball $840.00 4 HSK football $960.00
Figure2-22.QueryResult
SELECT * FROM stock ORDER BY manu_code, unit_price DESC
SELECT * FROM stock ORDER BY unit_price, manu_code DESC
SELECT * FROM stock ORDER BY manu_code DESC, unit_price
SELECT * FROM stock ORDER BY unit_price DESC, manu_code
Figure2-25 showshowthestatementsimplyselectsalldatainthe
customer_numcolumnintheorderstableand liststhecustomernumberson alltheorders,includingduplicates.
Theoutputincludesseveralduplicates becausesomecustomershaveplaced morethanoneorder.Sometimes youwanttoseeduplicaterowsina projection.Atothertimes,youwanttoseeonlythedistinctvalues,nothow ofteneachvalueappears.
Tosuppressduplicaterows, youcaninclude thekeyword DISTINCTor its synonymUNIQUEatthestart oftheselectlist,onceineachlevelofa query, asFigure2-26shows.
Toproducea morereadablelist,Figure2-26limitsthedisplaytoshoweach customernumberintheorderstableonlyonce,asFigure2-27shows. SELECT customer_num FROM orders
Figure2-24.Query customer_num 104 101 104 . . . 122 123 124 126 127
Figure2-25.QueryResult
SELECT DISTINCT customer_num FROM orders
SELECT UNIQUE customer_num FROM orders
Supposeyouarehandlingacustomer call,andyouwanttolocatepurchase ordernumberDM354331.Tolistallthepurchaseordernumbersintheorders table,usea statementsuchastheonethatFigure2-28shows.
Figure2-29 showshowthestatementretrievesdatainthepo_numcolumnin theorders table.
customer_num 101 104 106 110 111 112 115 116 117 119 120 121 122 123 124 126 127
Figure2-27.QueryResult
SELECT po_num FROM orders
Figure2-28.Query po_num B77836 9270 B77890 8006 2865 Q13557 278693 . . .
However,thelistisnotina usefulorder.Youcanaddan ORDERBYclause tosortthecolumndatainascendingorderandmakeiteasier tofindthat particularpo_num,asFigure2-31 shows.
Toselectmultiplecolumns fromatable,listthemintheprojectionlistinthe Projectionclause.Figure2-32 showsthattheorderinwhichthecolumns are selectedistheorderinwhichtheyareretrieved,fromlefttoright.
As“SortingonMultipleColumns”onpage2-12 shows,youcanusethe ORDERBYclausetosortthedatainascendingordescendingorderand performnestedsorts. Figure2-33 showsascendingorder.
SELECT po_num FROM orders ORDER BY po_num
Figure2-30.Query po_num 278693 278701 2865 429Q 4745 8006 8052 9270 B77836 B77890 . . .
Figure2-31.QueryResult
SELECT ship_date, order_date, customer_num, order_num, po_num
FROM orders
ORDER BY order_date, ship_date
WhenyouuseSELECTand ORDERBYonseveralcolumnsina table,you mightfindit helpfultouseintegerstorefertothepositionofthecolumnsin theORDER BYclause.Whenanintegerisan elementintheORDERBYlist, thedatabase servertreatsitasthepositionintheprojectionlist.For example, using3 intheORDERBYlist(ORDERBY3) referstothethirditeminthe projectionlist.ThestatementsinFigure2-34 retrieveand displaythesame data,asFigure2-35shows.
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
Figure2-33.QueryResult
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
Youcaninclude theDESC keywordintheORDERBYclausewhenyouassign integersto columnnames,asFigure2-36shows.
Inthis case,dataisfirstsortedindescendingorder byorder_dateand in ascendingorder bycustomer_num.
SelectingSubstrings
Toselectpartof thevalue ofacharactercolumn,includea substringinthe projectionlist.Supposeyourmarketing departmentisplanningamailingto yourcustomersandwantstheirgeographicaldistributionbased onzipcodes. Youcould writeaquerysimilartotheonethatFigure2-37 shows.
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
Figure2-35.QueryResult
SELECT customer_num, order_num, po_num, order_date FROM orders
ORDER BY 4 DESC, 1
Figure2-37 usesasubstringtoselectthefirst threecharacters ofthezipcode column(whichidentifythestate)andthefullcustomer_num,andlists them inascendingorderbyzipcode,asFigure2-38 shows.
ORDERBYandNon-EnglishData
Bydefault,Informixdatabaseservers usetheU.S.Englishlanguage environment,calledalocale,fordatabasedata.TheU.S.Englishlocale specifiesdatasortedincode-setorder.Thisdefaultlocale usestheISO8859-1 codeset.
Ifyourdatabasecontains non-Englishdata,youshouldstorenon-Englishdata inNCHAR(orNVARCHAR)columnstoobtainresultssortedbythe
language.TheORDER BYclauseshouldreturn dataintheorderappropriate tothatlanguage.Figure2-39usesa SELECTstatementwith anORDERBY clausetosearchthetable,abonnés,and toordertheselectedinformationby thedatainthenomcolumn.
Thecollationorderfortheresultsof thisquerycanvary, dependingonthe followingsystemvariations:
SELECT zipcode[1,3], customer_num FROM customer ORDER BY zipcode Figure2-37.Query zipcode customer_num 021 125 080 119 085 122 198 121 322 123 . . . 943 103 943 107 946 118
Figure2-38.QueryResult
SELECT numéro,nom,prénom FROM abonnés
ORDER BY nom
v WhetherthenomcolumnisCHARorNCHAR datatype. Thedatabase serversortsdatainCHARcolumns bytheorderthecharactersappearin thecodeset.ThedatabaseserversortsdatainNCHARcolumnsbythe orderthecharactersarelistedinthecollationportionof thelocale.
v Whetherthedatabaseserverisusingthecorrectnon-Englishlocalewhenit accessesthedatabase.Tousea non-Englishlocale,youmustsetthe
CLIENT_LOCALEand DB_LOCALEenvironmentvariablestothe appropriatelocalename.
ForFigure2-39toreturnexpectedresults,thenomcolumnshouldbe NCHAR datatypeinadatabase thatusesaFrenchlocale.Other operations,suchas lessthan,greaterthan,orequal to,arealsoaffectedbytheuser-specified locale.Formoreinformationonnon-Englishdataand locales,seethe IBM Informix:GLS User'sGuide.
Figure2-40 andFigure2-41showtwosamplesetsof output.
Figure2-40 followstheISO8859-1code-setorder,whichranksuppercase lettersbefore lowercaselettersandmoves namesthatcontainan accented character(Ålesund, Étaix,Ötker,andØverst)totheendofthelist. 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
Figure2-41 showsthatwhentheappropriatelocale fileisreferenced bythe databaseserver,namesincludingnon-Englishcharacters(Ålesund,Étaix, Ötker,and Øverst)arecollateddifferently thantheyareintheISO8859-1 codeset.Theyare sortedcorrectlyforthelocale.Itdoesnotdistinguish betweenuppercase andlowercase letters.