You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by tom_ <to...@web.de> on 2008/01/31 16:50:35 UTC
distinct and order by
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?
--
View this message in context: http://www.nabble.com/distinct-and-order-by-tp15206572p15206572.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: distinct and order by
Posted by tom_ <to...@web.de>.
Thank you for this possibility, though it is difficult for me to apply this
change because the sql query is one of a lot that are generated by an sql
query generator. I would have to hange this very complicated code and make
it even more complicated - and later on rechange the code back.
Knut Anders Hatlen wrote:
>
> tom_ <to...@web.de> writes:
>
>> 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)
>
> As a workaround, you may try to do SELECT DISTINCT in a subquery and do
> the ORDER BY in the outer query:
>
> select * from
> (select distinct t1.t1_id, t2.t2value1, t2.t2value2, t2.t2value3
> from t1, t2, t3 where ....) as sub(id, val1, val2, val3)
> order by lower(val1), lower(val2), lower(val3)
>
> --
> Knut Anders
>
>
--
View this message in context: http://www.nabble.com/distinct-and-order-by-tp15206572p15227107.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: distinct and order by
Posted by tom_ <to...@web.de>.
If a rearrangement of the SQL can do this, wouldn't the right place be in
Derby's SQL parser/optimizer?
Knut Anders Hatlen wrote:
>
> tom_ <to...@web.de> writes:
>
>> 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)
>
> As a workaround, you may try to do SELECT DISTINCT in a subquery and do
> the ORDER BY in the outer query:
>
> select * from
> (select distinct t1.t1_id, t2.t2value1, t2.t2value2, t2.t2value3
> from t1, t2, t3 where ....) as sub(id, val1, val2, val3)
> order by lower(val1), lower(val2), lower(val3)
>
> --
> Knut Anders
>
>
--
View this message in context: http://www.nabble.com/distinct-and-order-by-tp15206572p15242535.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: distinct and order by
Posted by tom_ <to...@web.de>.
I have found that for another query that also contains "distinct" and "order
by" the exception does not occur. For the first query the workaround works
well.
Knut Anders Hatlen wrote:
>
> tom_ <to...@web.de> writes:
>
>> 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)
>
> As a workaround, you may try to do SELECT DISTINCT in a subquery and do
> the ORDER BY in the outer query:
>
> select * from
> (select distinct t1.t1_id, t2.t2value1, t2.t2value2, t2.t2value3
> from t1, t2, t3 where ....) as sub(id, val1, val2, val3)
> order by lower(val1), lower(val2), lower(val3)
>
> --
> Knut Anders
>
>
--
View this message in context: http://www.nabble.com/distinct-and-order-by-tp15206572p15335366.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: distinct and order by
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
tom_ <to...@web.de> writes:
> 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)
As a workaround, you may try to do SELECT DISTINCT in a subquery and do
the ORDER BY in the outer query:
select * from
(select distinct t1.t1_id, t2.t2value1, t2.t2value2, t2.t2value3
from t1, t2, t3 where ....) as sub(id, val1, val2, val3)
order by lower(val1), lower(val2), lower(val3)
--
Knut Anders
Re: distinct and order by
Posted by tom_ <to...@web.de>.
I have created a new issue.
tom_ wrote:
>
> The problem is that I have to make a release that includes this sql
> select.
>
>
> Stanley Bradbury wrote:
>>
>> 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_ <to...@web.de> 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
>>>>
>>>>
>>>>
>>
>> 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.
>>
>>
>>
>>
>>
>
>
--
View this message in context: http://www.nabble.com/distinct-and-order-by-tp15206572p15224665.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: distinct and order by
Posted by tom_ <to...@web.de>.
The problem is that I have to make a release that includes this sql select.
Stanley Bradbury wrote:
>
> 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_ <to...@web.de> 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
>>>
>>>
>>>
>
> 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.
>
>
>
>
>
--
View this message in context: http://www.nabble.com/distinct-and-order-by-tp15206572p15214884.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: distinct and order by
Posted by Stanley Bradbury <St...@gmail.com>.
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_ <to...@web.de> 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
>>
>>
>>
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.
Re: distinct and order by
Posted by tom_ <to...@web.de>.
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_ <to...@web.de> 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
>
>
--
View this message in context: http://www.nabble.com/distinct-and-order-by-tp15206572p15210361.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: distinct and order by
Posted by Dy...@Sun.COM.
tom_ <to...@web.de> 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
RE: distinct and order by
Posted by "Williamson, Nick" <ni...@mentor.com>.
You can do it as long as the same column appears in both phrases.
Allowed:
select distinct project_id from projectproject order by project_id;
Not allowed:
select distinct project_id from projectproject order by name;
(ERROR 42879: The ORDER BY clause may not contain column 'NAME', since
the query
specifies DISTINCT and that column does not appear in the query result.)
HTH
Nick
-----Original Message-----
From: tom_ [mailto:tom12167@web.de]
Sent: 31 January 2008 15:51
To: derby-user@db.apache.org
Subject: distinct and order by
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?
--
View this message in context:
http://www.nabble.com/distinct-and-order-by-tp15206572p15206572.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.