CODEWITHSUNDEEP
mysqlsqlhibernate
Hayi
Hayi

Reputation: 6256

UNION with order by in HQL

How can I do this Mysql query in HQL :

select id , date from TABLE_1
union all
select id , date from TABLE_2

order by date asc

I know that the alternative to union is to make two separate requests but i want the union with the order by because it will help me a lot .

Upvotes: 3

Views: 911

Answers (2)

Rick James
Rick James

Reputation: 142472

Parentheses needed -- What you have is

( select id , date from TABLE_1 )
union all
( select id , date from TABLE_2
order by date asc )

What you want is

( select id , date from TABLE_1 )
union all
( select id , date from TABLE_2 )
order by date asc

Upvotes: 0

void
void

Reputation: 7890

you can separate the query to two query and then combine tow result lists into one and finally sort the final list with a Comparator:

Query query1 = session.createQuery("select t.id,t.dt from Table1 t");
Query query2 = session.createQuery("select t.id,t.dt from Table2 t");

List list1= query1.list();
List list2= query2.list();

list1.add(list2);


Collections.sort(list1, new Comparator<TABLE>(){
       public int compare (Table t1, Table t2){
           return t1.getDate().compareTo(t2.getDate());
       }
});

EDIT(after comments): in case of UNION (when query use UNION instead of union all and also without a Order By) we can use a HashSet:

Query query1 = session.createQuery("select t.id,t.dt from Table1 t");
Query query2 = session.createQuery("select t.id,t.dt from Table2 t");

List list1= query1.list();
List list2= query2.list();

list1.add(list2);

HashSet uniqueResult = new HashSet(list1);

Upvotes: 1

Related Questions