Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of lists@nabble.com designates
 216.139.236.158 as permitted sender)
Message-ID: <15214884.post@talk.nabble.com>
Date: Thu, 31 Jan 2008 13:10:37 -0800 (PST)
From: tom_ <tom12167@web.de>
To: derby-user@db.apache.org
Subject: Re: distinct and order by
In-Reply-To: <47A22DAE.1070200@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
References: <15206572.post@talk.nabble.com> <x1o31w7ykm9p.fsf@sun.com>
 <15210361.post@talk.nabble.com> <47A22DAE.1070200@gmail.com>


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_ <tom12167@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.