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 (athena.apache.org: domain of lists@nabble.com designates
 216.139.236.158 as permitted sender)
Message-ID: <15210361.post@talk.nabble.com>
Date: Thu, 31 Jan 2008 09:55:46 -0800 (PST)
From: tom_ <tom12167@web.de>
To: derby-user@db.apache.org
Subject: Re: distinct and order by
In-Reply-To: <x1o31w7ykm9p.fsf@sun.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>


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
> 
> 

-- 
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.