basic sql question

payawaljohn · #1 (**permalink**) 10-29-06, 09:54

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.

r937 · #2 (**permalink**) 10-29-06, 10:48

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

payawaljohn · #3 (**permalink**) 10-29-06, 10:55

"order by" also not allowed

r937 · #4 (**permalink**) 10-29-06, 11:02

tell your teacher that ORDER BY is allowed in the real world

payawaljohn · #5 (**permalink**) 10-29-06, 11:24

sure ill tell him...

r937 · #6 (**permalink**) 10-29-06, 11:37

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

Peter.Vanroose · #7 (**permalink**) 10-30-06, 02:18

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?)

Littlefoot · #8 (**permalink**) 10-30-06, 02:36

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.

Peter.Vanroose · #9 (**permalink**) 10-30-06, 02:39

Remarkable!

Just curious: do you know how Oracle is implementing the DISTINCT in this case?

Littlefoot · #10 (**permalink**) 10-30-06, 03:23

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:

Quote:

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!

r937 · #11 (**permalink**) 10-30-06, 05:32

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

r937 · #12 (**permalink**) 10-30-06, 05:34

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.

urquel · #13 (**permalink**) 10-30-06, 09:44

What about "GROUP BY"?? Won't that force a sort??

r937 · #14 (**permalink**) 10-30-06, 10:26

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"

Pat Phelan · #15 (**permalink**) 10-30-06, 15:12

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

Internet Services

Web Development

Digital Marketing

Consumer Tech