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: local policy)
Date: Tue, 15 Apr 2008 08:59:48 +0200
From: Knut Anders Hatlen <Knut.Hatlen@Sun.COM>
Subject: Re: Problem with derby 10.4
In-reply-to: <6d02bbccf27d3cc372dded82752dec22@imperiaonline.org>
Sender: Knut.Hatlen@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <wjo8bq4bk3h7.fsf@khepri41.norway.sun.com>
Organization: Sun Microsystems
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT
References: <6d02bbccf27d3cc372dded82752dec22@imperiaonline.org>
User-Agent: Gnus/5.110008 (No Gnus v0.8) Emacs/22.1 (usg-unix-v)

caxo <caxo@imperiaonline.org> writes:

> I have to take a certain number of rows ordered by a field.
> And the derby say me error: Syntax error: Encountered "ORDER"
>
> SELECT * FROM (
>    SELECT ROW_NUMBER() OVER () AS R,
>     playerId, 
>     playerName, 
>     points 
>    FROM ranks 
>    ORDER BY points DESC
>    )
>  AS TR WHERE R <= 10
>
> This is my query and I'm not shure is it a bug or I'm not doing something
> right.

It should work if you move the ORDER BY clause into the outer SELECT,
like this:

  SELECT * FROM (
    SELECT ROW_NUMBER() OVER () AS R,
           playerId,
           playerName,
           points
    FROM ranks) AS TR
    WHERE R <= 10 ORDER BY points DESC

-- 
Knut Anders