Search code examples
jpajpql

JPQL / JPA query to order entities based on the greatest/maximum of two columns?

I need something similar to this SQL query:

SELECT * FROM foo f ORDER BY GREATEST(f.bar_date, f.baz_date)

As not all RDBMS systems support the GREATEST function, ideally I would like the JPA implementation to generate the correct SQL query for the underlying database. The databases that I am targetting are Oracle, SQL Server, and PostgreSQL.

Solution

  • JPQL allows the FUNCTION operator to call a database function.

    Oracle and Postgres support GREATEST, I don't think SQL Server does, so for it you may be able to write your own function.

    You could also use a CASE function with >.