[
https://issues.apache.org/jira/browse/DERBY-3373?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12575771#action_12575771
]
Bryan Pendleton commented on DERBY-3373:
----------------------------------------
I agree, this was definitely caused by the DERBY-2351 changes.
A possible workaround would be to include the order by expressions
into the select list. That is, modify the query from:
select distinct airline_full from airlines order by lower(airline_full);
to
select distinct airline_full, lower(airline_full) from airlines order by
lower(airline_full);
> SQL "distinct" and "order by" needed together
> ---------------------------------------------
>
> Key: DERBY-3373
> URL: https://issues.apache.org/jira/browse/DERBY-3373
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.3.2.1
> Environment: Solaris Dev Express, Java 5
> Reporter: Thomas Vatter
> Priority: Blocker
> Fix For: 10.3.2.2
>
>
> I am pasting here the communication from the mailinglist. I am having a
> blocking and large problem with it because I have to make a release that
> needs the specified SQL query.
> tom_ wrote:
> > The errormessage is
> >
> > The ORDER BY clause may not specify an expression, since the query
> > specifies
> > DISTINCT
> > [Error Code: 20000]
> > [SQL State: 4287A]
> >
> > The statement is
> >
> > select distinct
> > t1.t1_id, t2.t2value1, t2.t2value2, t2.t2value3
> > from
> > t1, t2, t3
> > where
> > ...
> > order by lower(t2.t2value2) , lower(t2.t2value1) , lower(t2.t2value3)
> >
> >
> >
> >
> > Dyre.Tjeldvoll wrote:
> >
> >> tom_ <[EMAIL PROTECTED]> writes:
> >>
> >>
> >>> I am using "disctinct" because of some self-joins and also needed to add
> >>> an
> >>> "order by" clause. An error is shown. Is it not possible to use distinct
> >>> and
> >>> order by together?
> >>>
> >> I think it is allowed. Executing
> >>
> >> select distinct * from sys.systables order by tablename;
> >>
> >> in ij works just fine. Could you show the error message you get, and
> >> perhaps what the table looks like?
> >>
> >> --
> >> dt
> >>
> >>
> >>
> « [hide part of quote]
> Hi Tom -
> I see what you mean using the demo DB toursDB:
> ij> select * from airlines order by lower(airline_full);
> A&|AIRLINE_FULL |BASIC_RATE |DISTANCE_DISCOUNT
> |BUSINESS_LEVEL_FACTOR
> |FIRSTCLASS_LEVEL_FACT&|ECONOMY_SE&|BUSINESS_S&|FIRSTCLASS&
> -----------------------------------------------------------------------------------------------------------------------------------------------------------
>
> AA|Amazonian Airways |0.18 |0.03
> |0.5 |1.5 |20 |10 |5
> US|Union Standard Airlines |0.19 |0.05
> |0.4 |1.6 |20 |10 |5
> 2 rows selected
> ij> select distinct * from airlines order by lower(airline_full);
> ERROR 4287A: The ORDER BY clause may not specify an expression, since
> the query specifies DISTINCT.
> ij> select distinct airline_full from airlines order by lower(airline_full);
> ERROR 4287A: The ORDER BY clause may not specify an expression, since
> the query specifies DISTINCT.
> ij>
> I didn't find a JIRA enhancement to remove this restriction. I suggest
> you file an Enhancement request to remove the restriction reported by
> ERROR 4287A.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
