RANK() Oracle

[Jochen Schreiber]

Hello guys,

i have an sql statement which contains rank(). This statement comes
from oracle. Which is the translation for h2?

select * from ( select nt.*,rank() over(PARTITION by protocol_id order
by concentration desc) as ranking from (select r.protocol_id,
concentration, min(p.short_name) from hts.rsc_all_retests r,
hts.rin_protocol p ... nt ) where ranking < 3 order by 1,2;

I need a translation which works in h2 and in oracle databases.

Greetz

bladepit

[Noel Grandin]

rank() and partition are not yet implemented in H2.
Not sure when they will get implemented because you are the first person who has spoken up with a definite need for them :-)

But if you feel like providing patches, we would love to have them :-)

[Jochen Schreiber]

This is a very bad information for me...:-(

[Thomas Mueller]

Hi,

Most databases I tested don't support RANK(). I know PostgreSQL supports it, and Oracle, and probably MS SQL Server. But MySQL, Apache Derby, HSQLDB, SQLite, and H2 don't support it. I thought about a workaround, and the easiest one I found is below. I guess it's not very efficient however. For MySQL and H2, it would be more efficient to use variables (@rank := ...).

drop table test;

create table test(id int, c int);

create index idx_test_id_c on test(id, c);

insert into test values

(1, 20), (1, 20), (1, 30), (1, 30), (1, 40),

(2, 10), (2, 20), (2, 30), (2, 40), (2, 50),

(3, 10);

select id, c, rank from 

(select id, c, 

  (select 1 + count(*) from test t2 

  where t2.id = t.id and t2.c > t.c) rank

from test t) t where rank <= 2

order by id, c desc;

select id, c, rank from (

select id, c, rank() over(partition by id order by c desc) rank from test) t

where rank <= 2

order by id, c desc;

Regards,

Thomas

[Lukas Eder]

For the reference, I have encountered true window function support in any of these databases:

- DB2

- Postgres

- Oracle

- SQL Server

- Sybase SQL Anywhere

Oracle has by far the most functionality in this field. SQL Server is rather limited. I like your rank simulation though. I have never thought of this like that before. I might give this some thought and build in some window function simulation into jOOQ to increase compatibility between databases.

Cheers

Lukas

[Shammat]

Teradata also supports them and Firebird will in the next release.

SQL Server will have much better support with Denali (lag(), lead()
"rows between ...")

Actually PostgreSQL is a bit more advanced than Oracle as it has
everything that Oracle has (except some statistical aggregates) but
also has re-usable WINDOW definitions and I think the ROWS BETWEEN
part in Oracle is not as complete as with Postgres 9.1