basic sql question

using only the keywords
a. insert
b. select
c. delete
d. update
e. where
how do i sort a list.

(note that the keyword "sort" cannot be used)

take for example a list with 5 elements
a. 1782^12 + 1841^12 = 1922^12 (which disproves fermats last theorem)
b. 3987^12 + 4365^12 = 4472^12 (another proof of fermats error)
c. 1729 (smallest number expressible as the sum of two cubes)
d. largest known mersenne prime
e. pi*(10^40000) (the digit in the units place is 1)

it should yield c, a, b, e, d.is this some kind of homework question?

there is no "sort" keyword in SQL

however, there is the ORDER BY clause -- give that a try ;)|||"order by" also not allowed :eek:|||tell your teacher that ORDER BY is allowed in the real world|||:D

sure ill tell him...|||when your class gets around to reviewing the possible solutions to this question, i would be very interested in seeing the so-called "correct" answer|||What about "SELECT DISTINCT column_name FROM tbl" ?
Doesn't a DISTINCT guarantee ordering ?
(I know for sure it does on all RDBMS systems I have seen, but is this required behaviour in terms of the SQL language specs?)|||Oracle must be one of the databases you haven't seen, Peter :)SQL> select distinct ename from emp;

ENAME
----
ALLEN
JONES
TIGER
FORD
BURGLAR
CLARK
MILLER
SMITH
SCOTT
TURNER
ADAMS
BLAKE
KING
JAMES

14 rows selected.

SQL>Distinct didn't order those names.|||Remarkable!

Just curious: do you know how Oracle is implementing the DISTINCT in this case?|||Hm, now you got me thinking ... I checked Oracle documentation, but it says that DISTINCT does, well, what it does - ordering is never mentioned (or I couldn't find it).

However, Googling around found this Tuning queries (http://www.informit.com/articles/article.asp?p=27015&seqNum=8&rl=1) page. It says something like this:Oracle (and some other systems) may produce results in lname order. If you get the results you want, use your performance tools to measure the difference between this technique and using ORDER BY. However, this technique can fail if the indexes change. Be sure to document it and note it's a trick.So I tried it:select distinct ename from emp where ename >= ' '; - nothing happened; records were still shuffled. Then I grasped for "... if the indexes change" sentence, and created an index on 'ename' column and - voila!SQL> create index i1 on emp (ename);

Index created.

SQL> select distinct ename from emp where ename >= ' ';

ENAME
----
ADAMS
ALLEN
BLAKE
BURGLAR
CLARK
FORD
JAMES
JONES
KING
MILLER
SCOTT
SMITH
TIGER
TURNER

14 rows selected.

SQL>Records really are ordered!

When I ran the same query WITHOUT the DISTINCT keyword, the result was the same - records were ordered by 'ename'. So I broadened it with another column, added another condition into the WHERE clause and got different results: if the second column from the WHERE clause was indexed, records were NOT ordered. If I dropped that index, they were again ordered. Etc. etc.

It is a nice little trick; I should take some time to research it better (what happens when index changes, use of a more complicated WHERE clause, etc.), but - as a trick, it surely is interesting!

Thank you, Peter!|||... but is this required behaviour in terms of the SQL language specs?the SQL standard is pretty clear: if you want a particular ordering, you must use ORDER BY|||oh, and you guys should take your DISTINCT idea and apply it to the 5 elements in the list as given in post #1

i still don't see how e. comes before d.|||What about "GROUP BY"?? Won't that force a sort??|||What about "GROUP BY"?? Won't that force a sort??i cannot speak for oracle, but as this is the SQL forum and not the oracle forum, the answer, again, is "not necessarily"

:cool:|||Can you post the URL or a JPG of the assignment? I suspect that there are one or more critical pieces missing that are keeping us from finding a satisfactory solution.

I've found several possible solutions that acheive the desired results, but none of them satisfy me... Something is missing, subtly wrong, and that is bugging me!

-PatP|||As of all those little dirty undocumented Oracle tricks (DISTINCT, GROUP BY, indexed column in the WHERE clause, etc.), I'd rather stick to Rudy's axiom: if you want a particular ordering, you must use ORDER BY.|||take for example a list with 5 elements
a. 1782^12 + 1841^12 = 1922^12 (which disproves fermats last theorem)
b. 3987^12 + 4365^12 = 4472^12 (another proof of fermats error)
c. 1729 (smallest number expressible as the sum of two cubes)
d. largest known mersenne prime
e. pi*(10^40000) (the digit in the units place is 1)

it should yield c, a, b, e, d.

How are these elements stored? a. and b. look like equalities and not representations of numeric values. d. is not determinate. (The largest known value is dependent upon who you ask and subject to change as new values are "discovered") Is e. rounded after 40000 siginificant digits?|||good question

i'm guessing that they are stored as strings

'1729...' < '1782...' < '3987...' < 'larg...' < 'pi*(...'

which is why it should be c, a, b, d, e

:)|||i'm guessing that they are stored as strings
'1729...' < '1782...' < '3987...' < 'larg...' < 'pi*(...'
which is why it should be c, a, b, d, eOnly if collating sequence is ASCII or Unicode. In EBCDIC it's d < e < c < a < b.

(Mainframe is gradually moving to Unicode, but the default is still EBCDIC ...)|||OHHHH... i feel stupid! I was thinking of the resulting numeric values, not the actual string representations for the mathematical expressions.|||I was thinking of the resulting numeric values ...the thought crossed my mind briefly, but i figured anything to the power of 40000 is going to blow some registers...|||I used Microsof Exce and OpenOffice and, for a. and b.,

a. ((1782^12)+(1841^12))^(1/12))
i got 1922.

b. ((3987^12)+(4365^12))^(1/12))
i got 4472.

so now i am going to email andrew wiles, goro shimuya and yutaka taniyama
to inform them of their mistake.

c. exp(7.4553)

d. i attach 9808358-digit mersenne prime in .tar.gz format. it is about 4megabytes.

e. obviously, pi*(10^40000) decimal places is
31415926535897932384626433832795028841971693993751 058209749445923078164062862
08998628034825342117067982148086513282306647093844 6095505822317253594081284811
17450284102701938521105559644622948954930381964428 8109756659334461284756482337
86783165271201909145648566923460348610454326648213 3936072602491412737245870066
06315588174881520920962829254091715364367892590360 0113305305488204665213841469
51941511609433057270365759591953092186117381932611 7931051185480744623799627495
67351885752724891227938183011949129833673362440656 6430860213949463952247371907
02179860943702770539217176293176752384674818467669 4051320005681271452635608277
85771342757789609173637178721468440901224953430146 5495853710507922796892589235
42019956112129021960864034418159813629774771309960 5187072113499999983729780499
51059731732816096318595024459455346908302642522308 2533446850352619311881710100
03137838752886587533208381420617177669147303598253 4904287554687311595628638823
53787593751957781857780532171226806613001927876611 1959092164201989380952572010
65485863278865936153381827968230301952035301852968 9957736225994138912497217752
83479131515574857242454150695950829533116861727855 8890750983817546374649393192
55060400927701671139009848824012858361603563707660 1047101819429555961989467678
37449448255379774726847104047534646208046684259069 4912933136770289891521047521
62056966024058038150193511253382430035587640247496 4732639141992726042699227967
82354781636009341721641219924586315030286182974555 7067498385054945885869269956
90927210797509302955321165344987202755960236480665 4991198818347977535663698074
26542527862551818417574672890977772793800081647060 0161452491921732172147723501
41441973568548161361157352552133475741849468438523 3239073941433345477624168625
18983569485562099219222184272550254256887671790494 6016534668049886272327917860
85784383827967976681454100953883786360950680064225 1252051173929848960841284886
26945604241965285022210661186306744278622039194945 0471237137869609563643719172
87467764657573962413890865832645995813390478027590 0994657640789512694683983525
95709825822620522489407726719478268482601476990902 6401363944374553050682034962
52451749399651431429809190659250937221696461515709 8583874105978859597729754989
30161753928468138268683868942774155991855925245953 9594310499725246808459872736
44695848653836736222626099124608051243884390451244 1365497627807977156914359977
00129616089441694868555848406353422072225828488648 1584560285060168427394522674
67678895252138522549954666727823986456596116354886 2305774564980355936345681743
24112515076069479451096596094025228879710893145669 1368672287489405601015033086
17928680920874760917824938589009714909675985261365 5497818931297848216829989487
22658804857564014270477555132379641451523746234364 5428584447952658678210511413
54735739523113427166102135969536231442952484937187 1101457654035902799344037420
07310578539062198387447808478489683321445713868751 9435064302184531910484810053
70614680674919278191197939952061419663428754440643 7451237181921799983910159195
61814675142691239748940907186494231961567945208095 1465502252316038819301420937
62137855956638937787083039069792077346722182562599 6615014215030680384477345492
02605414665925201497442850732518666002132434088190 7104863317346496514539057962
68561005508106658796998163574736384052571459102897 0641401109712062804390397595
15677157700420337869936007230558763176359421873125 1471205329281918261861258673
21579198414848829164470609575270695722091756711672 2910981690915280173506712748
58322287183520935396572512108357915136988209144421 0067510334671103141267111369
90865851639831501970165151168517143765761835155650 8849099898599823873455283316
35507647918535893226185489632132933089857064204675 2590709154814165498594616371
80270981994309924488957571282890592323326097299712 0844335732654893823911932597
46366730583604142813883032038249037589852437441702 9132765618093773444030707469
21120191302033038019762110110044929321516084244485 9637669838952286847831235526
58213144957685726243344189303968642624341077322697 8028073189154411010446823252
71620105265227211166039666557309254711055785376346 6820653109896526918620564769
31257058635662018558100729360659876486117910453348 8503461136576867532494416680
39626579787718556084552965412665408530614344431858 6769751456614068007002378776
59134401712749470420562230538994561314071127000407 8547332699390814546646458807
97270826683063432858785698305235808933065757406795 4571637752542021149557615814
00250126228594130216471550979259230990796547376125 5176567513575178296664547791
74501129961489030463994713296210734043751895735961 4589019389713111790429782856
47503203198691514028708085990480109412147221317947 6477726224142548545403321571
85306142288137585043063321751829798662237172159160 7716692547487389866549494501
14654062843366393790039769265672146385306736096571 2091807638327166416274888800
78692560290228472104031721186082041900042296617119 6377921337575114959501566049
63186294726547364252308177036751590673502350728354 0567040386743513622224771589
15049530984448933309634087807693259939780541934144 7377441842631298608099888687
41326047215695162396586457302163159819319516735381 2974167729478672422924654366
80098067692823828068996400482435403701416314965897 9409243237896907069779422362
50822168895738379862300159377647165122893578601588 1617557829735233446042815126
27203734314653197777416031990665541876397929334419 5215413418994854447345673831
62499341913181480927777103863877343177207545654532 2077709212019051660962804909
26360197598828161332316663652861932668633606273567 6303544776280350450777235547
10585954870279081435624014517180624643626794561275 3181340783303362542327839449
75382437205835311477119926063813346776879695970309 8339130771098704085913374641
44282277263465947047458784778720192771528073176790 7707157213444730605700733492
43693113835049316312840425121925651798069411352801 3147013047816437885185290928
54520116583934196562134914341595625865865570552690 4965209858033850722426482939
72858478316305777756068887644624824685792603953527 7348030480290058760758251047
47091643961362676044925627420420832085661190625454 3372131535958450687724602901
61876679524061634252257719542916299193064553779914 0373404328752628889639958794
75729174642635745525407909145135711136941091193932 5191076020825202618798531887
70584297259167781314969900901921169717372784768472 6860849003377024242916513005
00516832336435038951702989392233451722013812806965 0117844087451960121228599371
62313017114448464090389064495444006198690754851602 6327505298349187407866808818
33851022833450850486082503930213321971551843063545 5007668282949304137765527939|||75175461395398468339363830474611996653858153842056 8533862186725233402830871123
28278921250771262946322956398989893582116745627010 2183564622013496715188190973
03811980049734072396103685406643193950979019069963 9552453005450580685501956730
22921913933918568034490398205955100226353536192041 9947455385938102343955449597
78377902374216172711172364343543947822181852862408 5140066604433258885698670543
15470696574745855033232334210730154594051655379068 6627333799585115625784322988
27372319898757141595781119635833005940873068121602 8764962867446047746491599505
49737425626901049037781986835938146574126804925648 7985561453723478673303904688
38343634655379498641927056387293174872332083760112 3029911367938627089438799362
01629515413371424892830722012690147546684765357616 4773794675200490757155527819
65362132392640616013635815590742202020318727760527 7219005561484255518792530343
51398442532234157623361064250639049750086562710953 5919465897514131034822769306
24743536325691607815478181152843667957061108615331 5044521274739245449454236828
86061340841486377670096120715124914043027253860764 8236341433462351897576645216
41376796903149501910857598442391986291642193994907 2362346468441173940326591840
44378051333894525742399508296591228508555821572503 1071257012668302402929525220
11872676756220415420516184163484756516999811614101 0029960783869092916030288400
26910414079288621507842451670908700069928212066041 8371806535567252532567532861
29104248776182582976515795984703562226293486003415 8722980534989650226291748788
20273420922224533985626476691490556284250391275771 0284027998066365825488926488
02545661017296702664076559042909945681506526530537 1829412703369313785178609040
70866711496558343434769338578171138645587367812301 4587687126603489139095620099
39361031029161615288138437909904231747336394804575 9314931405297634757481193567
09110137751721008031559024853090669203767192203322 9094334676851422144773793937
51703443661991040337511173547191855046449026365512 8162288244625759163330391072
25383742182140883508657391771509682887478265699599 5744906617583441375223970968
34080053559849175417381883999446974867626551658276 5848358845314277568790029095
17028352971634456212964043523117600665101241200659 7558512761785838292041974844
23608007193045761893234922927965019875187212726750 7981255470958904556357921221
03334669749923563025494780249011419521238281530911 4079073860251522742995818072
47162591668545133312394804947079119153267343028244 1860414263639548000448002670
49624820179289647669758318327131425170296923488962 7668440323260927524960357996
46925650493681836090032380929345958897069536534940 6034021665443755890045632882
25054525564056448246515187547119621844396582533754 3885690941130315095261793780
02974120766514793942590298969594699556576121865619 6733786236256125216320862869
22210327488921865436480229678070576561514463204692 7906821207388377814233562823
60896320806822246801224826117718589638140918390367 3672220888321513755600372798
39400415297002878307667094447456013455641725437090 6979396122571429894671543578
46878861444581231459357198492252847160504922124247 0141214780573455105008019086
99603302763478708108175450119307141223390866393833 9529425786905076431006383519
83438934159613185434754649556978103829309716465143 8407007073604112373599843452
25161050702705623526601276484830840761183013052793 2054274628654036036745328651
05706587488225698157936789766974220575059683440869 7350201410206723585020072452
25632651341055924019027421624843914035998953539459 0944070469120914093870012645
60016237428802109276457931065792295524988727584610 1264836999892256959688159205
60010165525637567856672279661988578279484885583439 7518744545512965634434803966
42055798293680435220277098429423253302257634180703 9476994159791594530069752148
29336655566156787364005366656416547321704390352132 9543529169414599041608753201
86837937023488868947915107163785290234529244077365 9495630510074210871426134974
59561513849871375704710178795731042296906667021449 8637464595280824369445789772
33004876476524133907592043401963403911473202338071 5095222010682563427471646024
33544005152126693249341967397704159568375355516673 0273900749729736354964533288
86984406119649616277344951827369558822075735517665 1589855190986665393549481068
87320685990754079234240230092590070173196036225475 6478940647548346647760411463
23390565134330684495397907090302346046147096169688 6885014083470405460742958699
13829668246818571031887906528703665083243197440477 1855678934823089431068287027
22809736248093996270607472645539925399442808113736 9433887294063079261595995462
62462970706259484556903471197299640908941805953439 3251236235508134949004364278
52713831591256898929519642728757394691427253436694 1532361004537304881985517065
94121735246258954873016760029886592578662856124966 5523533829428785425340483083
30701653722856355915253478445981831341129001999205 9813522051173365856407826484
94276441137639386692480311836445369858917544264739 9882284621844900877769776312
79572267265556259628254276531830013407092233436577 9160128093179401718598599933
84923549564005709955856113498025249906698423301735 0358044081168552653117099570
89942732870925848789443646005041089226691783525870 7859512983441729535195378855
34573742608590290817651557803905946408735061232261 1200937310804854852635722825
76820341605048466277504500312620080079980492548534 6941469775164932709504934639
38243222718851597405470214828971117779237612257887 3477188196825462981268685817
05074027255026332904497627789442362167411918626943 9650671515779586756482399391
76042601763387045499017614364120469218237076488783 4196896861181558158736062938
60381017121585527266830082383404656475880405138080 1633638874216371406435495561
86896411228214075330265510042410489678352858829024 3670904887118190909494533144
21828766181031007354770549815968077200947469613436 0928614849417850171807793068
10854690009445899527942439813921350558642219648349 1512639012803832001097738680
66287792397180146134324457264009737425700735921003 1541508936793008169980536520
27600727749674584002836240534603726341655425902760 1834840306811381855105979705
66400750942608788573579603732451414678670368809880 6097164258497595138069309449
40151542222194329130217391253835591503100333032511 1749156969174502714943315155
88540392216409722910112903552181576282328318234254 8326111912800928252561902052
63016391147724733148573910777587442538761174657867 1169414776421441111263583553
87136101102326798775641024682403226483464176636980 6637857681349204530224081972
78564719839630878154322116691224641591177673225326 4335686146186545222681268872
68445968442416107854016768142080885028005414361314 6230821025941737562389942075
71362751674573189189456283525704413354375857534269 8699472547031656613991999682
62824727064133622217892390317608542894373393561889 1651250424404008952719837873
86480584726895462438823437517885201439560057104811 9498842390606136957342315590
79670346149143447886360410318235073650277859089757 8272731305048893989009923913
50337325085598265586708924261242947367019390772713 0706869170926462548423240748
55036608013604668951184009366860954632500214585293 0950000907151058236267293264
53738210493872499669933942468551648326113414611068 0267446637334375340764294026
68297386522093570162638464852851490362932019919968 8285171839536691345222444708
04592396602817156551565666111359823112250628905854 9145097157553900243931535190
90210711945730024388017661503527086260253788179751 9478061013715004489917210022
20133501310601639154158957803711779277522597874289 1917915522417189585361680594
74123419339842021874564925644346239253195313510331 1476394911995072858430658361
93536932969928983791494193940608572486396883690326 5564364216644257607914710869
98431573374964883529276932822076294728238153740996 1545598798259891093717126218
28302584811238901196822142945766758071865380650648 7026133892822994972574530332
83896381843944770779402284359883410035838542389735 4243956475556840952248445541
39239410001620769363684677641301781965937997155746 8541946334893748439129742391
43365936041003523437770658886778113949861647874714 0793263858738624732889645643
59877466763847946650407411182565837887845485814896 2961273998413442726086061872
45545236064315371011274680977870446409475828034876 9758948328241239292960582948
61919667091895808983320121031843034012849511620353 4280144127617285830243559830
03204|||there are 4 more pages but the site owners might get angry if i post all of them. also, i have pi expressed in base 2 to 40000 decimal (about 150000 binary) places. if you want it you can specifically request for it from me via email|||please don't, we don't really need to see the whole thing

by the way, both a and b were equations

therefore they can only evaluate as TRUE or FALSE

now, how do TRUE and FALSE sort? is TRUE greater than FALSE, or vice-versa?|||using only the keywords
a. insert
b. select
c. delete
d. update
e. where
how do i sort a list.
...
it should yield c, a, b, e, d.
It cannot be a coincidence that the words "insert", "select", "delete", "update" and "where" sort alphabetically as c, a, b, d, e (where I just ignore (d) and (e) being switched)
;)|||Poor Fermat. I wonder if he knows that we are having a laugh at his expense.|||and the answer is?....

I wanna know :eek: