Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Oct 2006
    Location
    amara no meikyuu
    Posts
    15

    Unanswered: 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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2006
    Location
    amara no meikyuu
    Posts
    15
    "order by" also not allowed

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    tell your teacher that ORDER BY is allowed in the real world
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2006
    Location
    amara no meikyuu
    Posts
    15


    sure ill tell him...

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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?)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oracle must be one of the databases you haven't seen, Peter
    Code:
    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.

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Remarkable!

    Just curious: do you know how Oracle is implementing the DISTINCT in this case?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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 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:
    Code:
    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!
    Code:
    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!

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Peter.Vanroose
    ... 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Aug 2004
    Posts
    330
    What about "GROUP BY"?? Won't that force a sort??

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by urquel
    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"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •