Order by and union query

Hibernate ORM
1

Have to implement below union query using criteria api and tried to build it using the code snippet in page as reference but not sure on how to sort the results as there is no root on union query.
How to do that? Any idea?

SELECT cat FROM Cat cat WHERE cat.name = :name
UNION
SELECT cat FROM Cat cat WHERE cat.type = :type
ORDER BY name ASC LIMIT 1 OFFSET 1
2

Sorting can be done by position. If you want to do that, you will have to also select the name and order by position 2.

3

Thanks @beikov. In my case, sort fields are dynamic and have to select all fields from the entity.
So is it possible to identify the position by field name?

4

You define the position.

SELECT cat, cat.name, cat.age FROM Cat cat WHERE cat.name = :name
UNION
SELECT cat, cat.name, cat.age FROM Cat cat WHERE cat.type = :type
ORDER BY 2, 3 ASC LIMIT 1 OFFSET 1

This will order by name and age.