Re: SQL: Inconsistent "order by"

[Loyd Goodbar <loyd@xxxxxxxxxxxxxx>]

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/