Doubt in ROW_NUMBER function

[Raghunath <ra...@gmail.com>]

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 ?
please clarify my doubt...


Thanks
Raghunath N
if u never failed, u never lived ...

Re: Doubt in ROW_NUMBER function

[Knut Anders Hatlen <Kn...@Sun.COM>]

Raghunath <ra...@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