H2 - Alternative to Rank () and Row_Num ()

Question

I am using H2 database in my java application. It does not seem to support the rank() and row_num() functions since they are still in their list of roadmaps

Here is the request I'm trying to fulfill

 select * from ( select *, rank() over(partition by MSISDN order by PORT_IN_DATE desc) rank from TEST_PORTIN ) s where rank = 1 AND PORT_IN_DATE > '2012-01-16 23:20:27' ORDER BY PORT_IN_DATE 

Answer 1

You do not need RANK() for your specific request. You can rewrite it as follows:

 SELECT * FROM TEST_PORTIN t1 WHERE PORT_IN_DATE > '2012-01-16 23:20:27' AND NOT EXISTS ( SELECT * FROM TEST_PORTIN t2 WHERE t1.MSISDN = t2.MSISDN -- PARTITION BY clause translation AND t1.PORT_IN_DATE > t2.PORT_IN_DATE -- ORDER BY clause translation ) ORDER BY PORT_IN_DATE 

Or, a little weird (but not necessarily fast) using a quantitative comparison predicate

 SELECT * FROM TEST_PORTIN t1 WHERE PORT_IN_DATE > '2012-01-16 23:20:27' AND PORT_IN_DATE >= ALL ( SELECT PORT_IN_DATE FROM TEST_PORTIN t2 WHERE t1.MSISDN = t2.MSISDN ) ORDER BY PORT_IN_DATE