How to deal with union/order by differences between HSQLDB and MySQL?

Question

I am currently using hsqldb for testing and mysql for production. This combination has worked great for me on every recent project, but I've come across a situation where HSQLDB's syntax/dialect is different from MySQL, and I'm not really sure what the best strategy is to overcome it.

In mysql, you can write a union query like this:

select ...
union
select ...
order by created_on desc
limit 20 offset 0

However, in HSQLDB, it complains that order by clause has an error.

Is there any way I can write the query so that both databases like the syntax?

Answer 1

try this:

select * from (

select ...
union
select ...)a
order by a.created_on desc
limit 20 offset 0