Re: SQL: Inconsistent "order by"

[rob@xxxxxxxxx]

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
Loyd,

First of all let me clarify.  In your example, when you did order by 1,2
you did mean order by column 1 and then by column2 correct?  That is how
it should work, and that is how it does.  However, if you do
order by :col
and you wanted to change the value of col in order to change which column
sorted you can't.  For example, if the value of col was 1 it wouldn't sort
by column 1.  Instead it would sort by a hard '1'.  In this case then you
might as well substitute 'M' for col.  They would both be meaningless. Or,
was I missing something in your explanation?

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




Loyd Goodbar <loyd@blackrobes.net>
Sent by: midrange-l-admin@midrange.com
11/21/2002 06:02 PM
Please respond to midrange-l

        To:     midrange-l@midrange.com
        cc:
        Fax to:
        Subject:        Re: SQL:  Inconsistent "order by"


Here is a real-world example why I might use an ordinal number to order by
than a column name.

select
  xact_date as Transaction_Date,
  case when xact_code in ('MT23','MT25') then 'ISSUE'
    when xact_code in ('MT51') then 'RETURN'
  end as Transaction_Type,
  xact_qty as Transaction_Qty,
  stockno as Item_Number
from msdb1/stxns
where xact_code in ('MT23','MT25','MT51')
order by 1,2

Otherwise, I would need to

order by
   xact_date, /* this is ok */
  case when xact_code in ('MT23','MT25') then 'ISSUE'
    when xact_code in ('MT51') then 'RETURN'
  end /* why do I need to type complex SQL more than once? */

Summarization and transformation of data fields are complex enough that
you
will want to order by ordinals. Otherwise, in the above example, I must
remember to change both the select and the order by if I decide to modify
the
SQL statement.  Admittedly, I almost always use the column names (or
column
aliases), but complex select statements sometimes demand ordinals.

I'm not sure it's in the SQL92/SQL99 standard, but the major SQL servers
I've
used honor an ordinal number in the order by clause. (DB2, MS-SQL, MySQL,
PostgreSQL)

I'll have to respectfully disagree with the statement that "They said
order by
2 was like saying order by 'M'.  Valid, but totally meaningless."

A Google search for "sql order ordinal number" brings plenty of results
that
this is normal and expected behavior.

Loyd

On Wed, 20 Nov 2002 16:14:53 -0500, rob@dekko.com wrote:

>This is a multipart message in MIME format.
>--
>[ Picked text/plain from multipart/alternative ]
>IBM clarified this by saying that when you try to use a parameter marker
>it tried to sort by the value of the marker, not the column number that
it
>represented.  They said order by 2 was like saying order by 'M'.  Valid,
>but totally meaningless.  Why anyone would want to sort by a hard value
is
>beyond me.

--
"Why, you can even hear yourself think." --Hobbes
"This is making me nervous. Let's go in." --Calvin
loyd@blackrobes.net  ICQ#504581  http://www.blackrobes.net/
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.