order by before group by in mysql
December 18, 2014
Posted by on
We have two entities: Activity 1:n Conversation. I need to get the latest conversation for each of the activity. The MySql doesn’t allow me to put order by before the group by.
I found a workaround from this :
So I get something like:
SELECT * FROM ( SELECT * FROM OP_TICKET_CONVERSATION WHERE ACTIVITY_ID IN (ticketsIds) ORDER BY OBJECT_ID DESC) AS tc GROUP BY tc.ACTIVITY_ID
This works but hibernate returns Object type, we need to specify the root entity:
this could be done by adding “addEntity”.
List conversationsList = em.createSQLQuery(sqlString).addEntity(TicketConversation.class).list();