Menu
I'm using PARTITION BY clause to do sorting on the result. Details for using PARTITION BY is mentioned in this question Sql Order by on multiple column. It works fine when i run in Oracle. I'm using H2 db for my unit test cases. When i run the same query on H2 db, it doesn't work. Is it known issue in H2? Is there any alternate solution which can work in Oracle and H2 both.
626
The SQL PARTITION BY expression is a subclause of the OVER clause, which is used in almost all invocations of window functions like AVG() , MAX() , and RANK() . As many readers probably know, window functions operate on window frames which are sets of rows that can be different for each record in the query result.
Mainly, H2 database can be configured to run as inmemory database, which means that data will not persist on the disk. Because of embedded database it is not used for production development, but mostly used for development and testing.
Partitioning is the database process where very large tables are divided into multiple smaller parts. By splitting a large table into smaller, individual tables, queries that access only a fraction of the data can run faster because there is less data to scan.
Multi-Version Concurrency Control (MVCC) That means, if connection A updates a row but doesn't commit this change yet, connection B will see the old value. Only when the change is committed, the new value is visible by other connections (read committed).
I don't think H2 supports window functions (aka analytic functions). However, you can do the query in the link using standard SQL:
SELECT t.*
FROM yourtable t join
(select vendorname, max(incidentdate) as maxdate
from yourtable yt
group by vendorname
) vn
on vn.vendorname = yt.vendorname
ORDER BY vn.maxDate DESC, t.VendorName ASC, t.IncidentDate DESC;
Although this should run in both environments, the over form probably performs better in SQL.
52
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
