Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (herse.apache.org: domain of yipng168@gmail.com designates
 64.233.184.239 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=llfK4/GJMvFK8rAIc0LSEUKtVqXEJ0BZdiZ2WrRF/sx9lCXczwwsDo4uOfpxktdYeRK1RjXeVDsAuG3s5TI8h7gchTzP1NWoALanBZkJwiDBsxwPU8I+vlGePEMNAauZhyaLGgqAhip5hDXZObnI9u6VAD9aYpY4vrAOg7pj0Lg=
Message-ID: <bcbfb06b0702191028n464eec9emac6159903a85b8b9@mail.gmail.com>
Date: Mon, 19 Feb 2007 10:28:12 -0800
From: "Yip Ng" <yipng168@gmail.com>
To: derby-dev@db.apache.org
Subject: Re: Need help on ORDER BY with expressions
In-Reply-To: <9f40b500702161332x67a477e4ke15a00b6ab29cfef@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <9f40b500702161332x67a477e4ke15a00b6ab29cfef@mail.gmail.com>

On 2/16/07, Laura Stewart <scotsmatrix@gmail.com> wrote:

> 1. The type of expressions that are allowed. My impression is that
> they are the Boolean expressions (as opposed to TableExpressions or
> SelectExpressions)?

ORDER BY expression is not limited to boolean expression only.  It can contain
common value expression such as numeric, string, datetime, etc.  It can also
have row value expression such as a scalar subquery, case expression, etc.


> 2. I need to understand how the Syntax for ORDER BY should appear.
> Should it just be "Expressions" added immediately after ColumnPosition
> (in both places in the Syntax? Or should it be specific type of
> expression (TableExpression, SelectExpression, Boolean Expression).
> Are there limitations as to the types of expressions allowed? Boolean,
> concatenation?

Perhaps the name sort-key expression would be more appropriate?
Adding it after Column Position is fine.

> 3. Description of the expressions allowed (will appear below the
> syntax) This will depend on what type of expressions are allowed, but
> one thing that the other parameters mention is if the column needs to
> be in the select list. I need help describing the expressions
> parameter.


> 4. Whatever type of expressions are allowed, that topic in the Derby
> Ref Guide needs to be updated too. Are there any limitations for ORDER
> BY? For example, the Boolean expressions topic mentions that there are
> limitations with check constraints.
>

Yes, there are restrictions.  If SELECT DISTINCT is specified or the
SELECT statement contains a GROUP BY clause, the ORDER BY columns need
to appear in the SELECT list.

I think there is a bug in Derby in the DISTINCT with ORDER BY case and
I have filed a jira for this.  See DERBY-2351 for further details.

Hope this helps.

Regards,
Yip Ng