| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: order by
Saturday, December 27, 2003, 11:14:25 AM, bulbultyagi_at_now-india.net.in (bulbultyagi_at_now-india.net.in) 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:jonathan@gennick.com
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 Oracle-article-request_at_gennick.com 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: jonathan_at_gennick.com
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: ListGuru_at_fatcity.com (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). Received on Sat Dec 27 2003 - 11:44:40 CST
 |
 |