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)
Message-ID: <49B093E7.8020608@nuix.com>
Date: Fri, 06 Mar 2009 14:09:27 +1100
From: Daniel Noll <daniel@nuix.com>
Organization: Nuix Pty Ltd
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
MIME-Version: 1.0
To: Derby Users Mailing List <derby-user@db.apache.org>
Subject: Order the results, *and* slice them
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi all.

I'm trying to use ROW_NUMBER() in conjunction with ORDER BY and not 
having a lot of luck.

This is the query I'm using, which I found on the Derby wiki:

   SELECT * FROM (
     SELECT ROW_NUMBER() OVER(ORDER BY startdate ASC) AS row,
            id, startdate, enddate, ...
     FROM HistoryRecord
   ) AS tmp
   WHERE row >= ? AND row < ?

I have also tried what I consider to be the intuitive way:

   SELECT * FROM (
     SELECT ROW_NUMBER() OVER() AS row, id, startdate, enddate, ...
     FROM HistoryRecord
     ORDER BY startdate ASC
   ) AS tmp
   WHERE row >= ? AND row < ?

Both of these give an error at ORDER BY.  Am I doing something wrong, or 
is this a missing part of the ROW_NUMBER() feature?

Daniel


-- 
Daniel Noll                            Forensic and eDiscovery Software
Senior Developer                              The world's most advanced
Nuix                                                email data analysis
http://nuix.com/                                and eDiscovery software