FAQ
Hello list,
Can someone please explain to me why the following order by clauses are
valid and yield the same results :

select empno, deptno from emp
order by sqrt (1);

and

select empno, deptno from emp
order by sqrt ( 3.14234 );

The docs say that in the order by clause you could specify only (a) column
names or (b) positional parameters or (c) expressions involving the columns

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
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).

Search Discussions

  • Gudmundur Josepsson at Dec 27, 2003 at 4:59 pm
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

    Because both expressions evaluate to
    1 and cause the output to be sorted by the first column?  Oracle seems
    to ignore the values after the decimal point.

    SQL> select a, c from x
    2  where rownum < 10  3  order by 1.7;

    A C----------
    -      4861 Y
    4862 N      4863
    Y      4864 N
    4865 Y      4866
    N      4867 Y
    4868 N      4869 Y

    9 rows selected.

    SQL> select a, c from x
    2  where rownum < 10  3  order by 2.7;

    A C----------
    -      4862 N
    4864 N      4868
    N      4866 N
    4861 Y      4869
    Y      4867 Y
    4865 Y      4863 Y

    9 rows selected.


    Gudmundur



    ----- Original Message -----
    From: <bulbultyagi@now-india.net.in>
    To: "Multiple recipients of list ORACLE-L"
    <ORACLE-L@fatcity.com>
    Sent: Saturday, December 27, 2003 4:14
    PM
    Subject: order by
    Hello list,> Can someone please explain to me why the
    following order by clauses are> valid and yield the same results
    :> > select empno, deptno from emp> order by sqrt (1);> > and> > select empno, deptno from emp>
    order by sqrt ( 3.14234 );> > The docs say that in the order by
    clause you could specify only (a) column> names or (b) positional
    parameters or (c) expressions involving the columns> > > --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net> --
    Author: <bulbultyagi@now-india.net.in>   INET: bulbultyagi@now-india.net.in>
    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@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).>

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Gudmundur Josepsson
    INET: gbj_at_index.is

    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).
  • Jonathan Gennick at Dec 27, 2003 at 5:44 pm
    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).
  • Stephane Faroult at Dec 28, 2003 at 10:04 pm

    bulbultyagi_at_now-india.net.in wrote:

    Hello list,
    Can someone please explain to me why the following order by clauses are
    valid and yield the same results :

    select empno, deptno from emp
    order by sqrt (1);

    and

    select empno, deptno from emp
    order by sqrt ( 3.14234 );

    The docs say that in the order by clause you could specify only (a) column
    names or (b) positional parameters or (c) expressions involving the columns
    A constant falls under the c) category. It's an expression, which
    involves anything you want. That said, I fail to see any practical use
    .

    --
    Regards,

    Stephane Faroult
    Oriole Software
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Stephane Faroult
    INET: sfaroult_at_oriole.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).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 27, '03 at 4:14p
activeDec 28, '03 at 10:04p
posts4
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase