[
https://issues.apache.org/jira/browse/DERBY-3373?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dyre Tjeldvoll updated DERBY-3373:
----------------------------------
Fix Version/s: 10.4.0.0
I added 10.4 as a fix version. 10.3.2.2 is already listed, so if possible, I
think it would be good to merge the fix to 10.3 as well.
> SQL "distinct" and "order by" needed together
> ---------------------------------------------
>
> Key: DERBY-3373
> URL: https://issues.apache.org/jira/browse/DERBY-3373
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.2.1
> Environment: Solaris Dev Express, Java 5
> Reporter: Thomas Vatter
> Assignee: Bryan Pendleton
> Priority: Blocker
> Fix For: 10.3.2.2, 10.4.0.0
>
> Attachments: allowExpressions.diff, mergeWith2351.diff
>
>
> 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.
