ROWNUM Function Behavior

[TerryG]

Hi Folks -- I am a very new H2 user (my experience so far has been
excellent), and have run into what might be my first minor issue,
which is the behavior of the ROWNUM() function. As stated in the
documentation, "The first row has the row number 1, and is calculated
*before* ordering and grouping the result set" (the asterisks are
mine). Another database ($QL $erver) assigns row numbers in a similar
manner, but *after* ordering. The latter enables you to use the row
number to JOIN a given row with the next and previous rows, which is
very handy.

So....is there any way to get ROWNUM() to calculate the row number
after the ordering of rows? If not, is there another mechanism in H2
which enables JOINs as described above?

Thanks in advance for any thoughts or suggestions...best
regards...TerryG

[Sergi Vladykin]

You can do something like
select *, rownum()
from
(select *
from ...
order by ...
)
but I think of course that current behavior of rownum in h2 is
incorrect

[TerryG]

Sergi -- your suggestion works perfectly, thank you! Now, when you
say "current behavior of rownum in h2 is incorrect", are you saying it
should it should assign the row numbers after the ordering, as per my
original post? But in any event, I'm back on track and thanks again
for your help...best regards...TerryG

[Sergi Vladykin]

Hm... I was surprised but oracle works like h2...
so may be I was wrong about incorrectness...
I don't know who is right mssql or oracle in
this implementation.. mysql and postgres
has no such feature to solve this by majority
of votes..

[TerryG]

I notice in the Roadmap on the the H2 site, there is mention of a
ROW_NUMBER() in the Priority 2 section, with the brief description
"not the same as ROWNUM". Perhaps this is the function which assigns
row numbers after ordering? The syntax function in $QL $erver is
ROW_NUMBER() OVER (ORDER BY ...) , so perhaps that's what they have in
mind with the reference to ROW_NUMBER() in the Roadmap.

[Thomas Mueller]

Hi,

It is possible using a trick:

drop table test;
create table test(id int);
insert into test values(1), (10), (3), (2);
select rownum(), * from test order by id; -- incorrect
select rownum, * from (select * from test order by id); -- correct

> current behavior of rownum in h2 is incorrect

I don't think it's incorrect. It matches the documentation, and as far
as I know it works like Oracle.

Anyway, this is quite a common problem. I will change the
documentation of ROWNUM to:

Returns the number of the current row. This function is supported for SELECT
statements, as well as for DELETE and UPDATE. The first row has the row number
1, and is calculated before ordering and grouping the result set.
To get the row number after ordering and grouping, use a subquery.

SELECT ROWNUM(), * FROM TEST;
SELECT ROWNUM(), * FROM (SELECT * FROM TEST ORDER BY NAME);

> MS SQL Server

"Row_Number() Over (Order By SalesOrderID)" - H2 doesn't support this
yet. But as more and more databases support it, it would be a good
idea to support it in H2 as well. As far as I know, Derby support
ROW_NUMBER() OVER() in the latest version, which is basically the same
as ROWNUM().

Regards,
Thomas

[Thomas Kellerer]

On 3 Aug., 21:10, Thomas Mueller <thomas.tom.muel...@gmail.com> wrote:
> I don't think it's incorrect. It matches the documentation, and as far
> as I know it works like Oracle.

Right, this is exactly the way Oracle's ROWNUM behaves (although in
Oracle it's not a function, so ROWNUM() does not work),

> "Row_Number() Over (Order By SalesOrderID)" - H2 doesn't support this
> yet. But as more and more databases support it, it would be a good
> idea to support it in H2 as well. As far as I know, Derby support
> ROW_NUMBER() OVER() in the latest version, which is basically the same
> as ROWNUM().

The row_number() is a so called "windowing functions" and behaves
completely different to Oracle's ROWNUM. Especially because you can
have the row_number() on only a part of the result set (using
partition by) and maybe a different sort order than the base select
(using order by in the over() clause).

Oracle also supports windowing functions (called analytical functions)
and thus also supports row_number().

To my knowledge only Oracle and Postgres support a more or less
complete set of ANSI windowing functions. SQL Server only supports
row_number(), rank() and dense_rank().

Derby does not support the full syntax for windowing functions as it
lacks the "partition by" and "order by" for the over() clause. so itss
row_number() cannot really be compared to Oracle's, Postgres' or SQL
Server's row_number()

[TerryG]

I used the subquery approach suggested above to address the issue
raised by my initial post and it works just fine. Now that I know the
trick, my requirement for something like ROW_NUMBER() OVER (ORDER
BY ...) is significantly diminished. Thanks to all for their help
with this! Best regards...TerryG