[FAQ] Order by woes

[Ruslan Zasukhin]

On 30/1/08 6:30 PM, "Mr. Bart Pietercil" <bart.pietercil at gmail.com> wrote:

Hi Bart,

Actually this is FAQ related to SQL Standard
We follow it,

It says: 
    you can ORDER BY only on EXPR listed in the SELECT.

MySQL, yes, allow use a field which is not listed...

Complain -- yes, as I remember must be error thrown.
    Ivan ?


> This query:
> select objptr_jobticket_holder->user_lastname as 'Sales Agent
> Lastname',objptr_jobticket_holder->user_Firstname as 'Sales Agent
> Firstname',
> objptr_jobticket_holder->user_mailaddress as 'email',
> objptr_jobticket_holder->user_logonname as
> 'Logonname',objptr_jobticket_holder->user_password as 'Password',
> tbl_jobtickets.objptr_cells->cell_name AS 'Cellname', objptr_cells
> from tbl_jobticket_holders inner join tbl_jobtickets on
> objptr_jobticket = tbl_jobtickets.RecID
> order by objptr_cells
> 
> 
> when in the select the last field 'objptr_cells' is not included the
> cursor is not ordered as expected (I think he is not ordered at all
> but not sure).
> Nothing is reported in the console.
> 
> If ordered by needs the field 'objptr_cells' in order to order (sorry
> could not resist) then it should complain
> If ordered by does not need this field (because it is already
> mentioned in tbl_jobtickets.objptr_cells) it should order (which it
> does not)
> 
> What gives ?

-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]