You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Laura Stewart <sc...@gmail.com> on 2007/02/16 22:32:05 UTC
Need help on ORDER BY with expressions
The JIRA issue DERBY-264 is for documenting the updates to the ORDER
BY clause which allows expressions ( DERBY-134 ). The comments on
DERBY-264 show some examples, but I need additional help understanding
what needs to be documented:
1. The type of expressions that are allowed. My impression is that
they are the Boolean expressions (as opposed to TableExpressions or
SelectExpressions)?
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?
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.
Thanks!
--
Laura Stewart
Re: Need help on ORDER BY with expressions
Posted by Yip Ng <yi...@gmail.com>.
> Laura Stewart wrote:
>I need some help understanding an example that was posted in DERBY-264.
>The example is:
>
SELECT i, j FROM t ORDER BY f ( j )
>
>I understand all but " f ". Is that supposed to be "function"?
>
Hi Laura:
I posted my comments regarding the changes and examples on the jira
entry (DERBY-264).
Regards,
Yip Ng
Re: Need help on ORDER BY with expressions
Posted by Stanley Bradbury <St...@gmail.com>.
Laura Stewart wrote:
> I need some help understanding an example that was posted in DERBY-264.
> The example is:
>
> SELECT i, j FROM t ORDER BY f ( j )
>
> I understand all but " f ". Is that supposed to be "function"?
>
> It would be better if I could see a real world example of using this
> type of expression in the ORDER BY clause.
>
> Thanks!
> Laura
= = SNIP = =
Hi Laura -
My guess is that you are correct and the f stands for 'any function
name'. My recommendation is that the you use labels of the other
non-SQL-command objects in the example like:
SELECT <col-1>, <col-2> FROM <table1> ORDER BY
<function-identifier>(<col-2>)
Please check with whomever provide this example to be sure I am not
missing something esoteric here.
Re: Need help on ORDER BY with expressions
Posted by Laura Stewart <sc...@gmail.com>.
I need some help understanding an example that was posted in DERBY-264.
The example is:
SELECT i, j FROM t ORDER BY f ( j )
I understand all but " f ". Is that supposed to be "function"?
It would be better if I could see a real world example of using this
type of expression in the ORDER BY clause.
Thanks!
Laura
On 2/19/07, Laura Stewart <sc...@gmail.com> wrote:
> Thanks Yip, this helped alot. I will be posting a patch for DERBY-264
> very soon.
> I'd appreciate it if you could take a look. Especially at the examples...
>
> Laura
>
> On 2/19/07, Yip Ng <yi...@gmail.com> wrote:
> > On 2/16/07, Laura Stewart <sc...@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
> >
>
>
> --
> Laura Stewart
>
--
Laura Stewart
Re: Need help on ORDER BY with expressions
Posted by Laura Stewart <sc...@gmail.com>.
Thanks Yip, this helped alot. I will be posting a patch for DERBY-264
very soon.
I'd appreciate it if you could take a look. Especially at the examples...
Laura
On 2/19/07, Yip Ng <yi...@gmail.com> wrote:
> On 2/16/07, Laura Stewart <sc...@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
>
--
Laura Stewart
Re: Need help on ORDER BY with expressions
Posted by Yip Ng <yi...@gmail.com>.
On 2/16/07, Laura Stewart <sc...@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