Re: Repeated columns in an ORDER BY clause?

Pravin Rangnekar <2clicks_at_winternet.com> wrote:
>Gautam Bhargava wrote:
>> Is it legal to repeat column names in an ORDER BY cluase. In other words,
>> is the following a legal SQL query?
>>
>> SELECT a, b, c FROM myTable
>> ORDER BY a, b, a
>>
>> Sybase 10.0 flags an error on this.
>> Oracle 7.2 processes it just fine.
>
>Why do you want to do this? What are trying to achieve? Second occurance
>of a, in the given example, will not have any effect on the order by as
>by then it will be already sorted in that order.
>
>Pravin Rangnekar
>Click Click, Inc.

Nobody would repeat the fields if the SQL is hard coded by hand. But a front-end application may well generate such statements at runtime. For example, at the front end, I may code:

        select ... order by :my_var1, :my_var2, :my_var3

The three :my_var front-end variables might repeat themselves at runtime. It'll be a headache if I have to check for repetition before I submit such a statment.

Anyway, System 11, like system 10, also gives an error when the columns are repeated. IMHO, the server shouldn't complain about such cases because there's nothing fundementally wrong with such statements. The optimizer can just ignore the subsequent repeated columns.

-- 

Q Vincent Yin                   |       Repeat
umyin_at_mctrf.mb.ca               |          delete(next->bug);
                                |       Until 0 = 1;