How to do union/unionAll with order for different queries?

[ktalebian]

I can do the following:

 SqlBuilder.select(columName)
            .from(tableName)
            .where(userId, isEqualTo(id))
            .and(type, isEqualTo("type1"))
            .unionAll()
            .select(columnName)
            .from(tableName)
            .where(userId, isEqualTo(id))
            .and(type, isEqualTo("type2"));

However, I need to do an orderBy and a limit on each queries individually. If I add a .orderBy().limit() then I cannot do a .unionAll

How do I achieve this then?

[jeffgbutler]

If I understand your request properly, then I don't think that would be valid SQL. I've run a couple of tests to confirm with HSQLDB. For example, this causes a syntax error in HSQLDB:

select * from AnimalData where id < 10 limit 5
union all
select * from AnimalData where id > 20 limit 3

If I've misunderstood your request, please show an example of SQL that works and let me know what database you are using.

[ktalebian]

What you have is not a valid query, but this would be a valid query:

(select * from AnimalData where id < 10 limit 5)
union all
(select * from AnimalData where id > 20 limit 3);

We are currently using that query in the XML file - trying to replace it with dynamic SQL

[jeffgbutler]

Got it. I think I can make this happen.