Saturday, December 27, 2003, 11:14:25 AM, [EMAIL PROTECTED] ([EMAIL PROTECTED]) wrote:
bnini> Hello list, Can someone please explain to me why the
bnini> following order by clauses are valid and yield the
bnini> same results :
bnini> select empno, deptno from emp
bnini> order by sqrt (1) ;
The clauses are constants. I'm not entirely convinced that
you'd always get the "same results". Think of adding sqrt(1)
to each row returned by the query, and then sorting the rows
on that column. Within that sort, you could end up with many
different suborderings. For example, both the following
result sets meet the sort criteria:
empno deptno [sqrt(1)]
1 2 1
2 3 1
empno deptno [sqrt(1)]
2 3 1
1 2 1
bnini> The docs say that in the order by clause you could specify only (a) column
bnini> names or (b) positional parameters or (c) expressions involving the columns
This is just a minor doc flaw. Probably the writer didn't
think about expressions not involving columns. Once you get
into expressions, it'd be pretty difficult to enforce a rule
that an expression needed to involve a column in any
meaningful way. For example:
select empno, deptno from emp
order by substr('1' || to_char(empno),1,1);
This particular ORDER BY expression manages to include a
column in a completely useless manner.
Probably the doc-writer should have used different words,
but the thought that someone would sort by a constant was
likely far from the writer's mind.
Best regards,
Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED]
Join the Oracle-article list and receive one
article on Oracle technologies per month by
email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article,
or send email to [EMAIL PROTECTED] and
include the word "subscribe" in either the subject or body.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Gennick
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
