db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Doubt in ROW_NUMBER function
Date Tue, 24 Jun 2008 08:48:02 GMT
Raghunath <raghu.nandy@gmail.com> writes:

> Hi,
>
> I need a query to restrict the row count in derby. luckily it got
> implemented in derby 10.4.1.3 but there are few limitations.
>
> one of the limitations as follows..
> -------
> http://db.apache.org/derby/docs/dev/ref/rreffuncrownumber.html
> the above doc is saying, *we cant use where clause along with ROW_NUMBER
> function *
>
> SELECT * FROM (
>    SELECT
>      ROW_NUMBER() OVER () AS R,
>      T.*
>    FROM T
> ) AS TR
>  WHERE R <= 10;
>
> ====================
> ----------------------
>
> But I modified the query to en-corporate where clause..It's still working
> fine with where clause.
> *SELECT * FROM (
>    SELECT
>      ROW_NUMBER() OVER () AS R,
>      g.*
>    FROM (select * from APP.CHANNEL where id>294912 ) as g
> ) AS TR
>    WHERE R <= 10;
>
> *
> My doubt is , is there any performance hit ?

No, the above query should be perfectly fine. The limitation is just a
syntactical one. Derby's parser doesn't accept

  SELECT ROW_NUMBER() OVER (), T.* FROM T WHERE ROW_NUBMER() OVER () <= 10

Using an alias as you did is the recommended way to do this in Derby,
and Derby will know that it should only look at the 10 first matching
rows in APP.CHANNEL.

-- 
Knut Anders

Mime
View raw message