SQL with rank and section

Question

I need to execute this sql:

select * from (select nt.*, rank() over (partition by feld0 order by feld1 desc) as ranking from (select bla from test) nt) where ranking < 3 order by 1,2 

This sql works fine in my oracle database, but in the h2 database, which I sometimes use, it does not work because the ranking and section are not defined.

So I need to convert this sql so that it works in h2 and oracle.

I want to use java to execute this sql. So is it possible to split this sql into different sqls without rank and section? And then process it using java?

Answer 1

If feld1 is unique in feld0 partitions, you can:

 select * , ( select count(*) from YourTable yt2 where yt2.feld0 = yt1.feld0 -- Same partition and yt2.feld1 <= yt1.feld1 -- Lower or equal rank ) as ranking from YourTable yt1