CODEWITHSUNDEEP
javasqlhibernatejpa
shay
shay

Reputation: 1357

JPA, playframework, select order by before group by

How can I write a query in "JPA" that will ORDER BY before the GROUP BY?

For example :

select m from Message m where m.id = (
  select distinct m2.conversation_id
    from Message m2
   where m2.from = 100 or m2.to = 100
)
group by m.conversation_id order by m.date desc

I want the last (most recent) messages for each conversation id.

Upvotes: 1

Views: 2655

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753505

Succinctly, you can't put the ORDER BY before the GROUP BY; SQL requires the ORDER BY after the GROUP BY.

However, you can achieve the result you require - either using the long-winded method below or by structuring your query with a different ORDER BY clause:

ORDER BY m.conversation_id, m.date DESC

In the code that fetches the data from the DB, you select the first row for each new conversation ID that is returned.

As always, I build the query in stages. First, find the most recent message timestamp for each conversation ID that involved 'user ID 100':

SELECT m2.conversation_id, MAX(m2.date) AS date
  FROM Message AS m2
 WHERE m2.from = 100 OR m2.to = 100
 GROUP BY m2.conversation_id

Now, join that with the original table to get the rows of interest:

SELECT m1.*
  FROM Message AS m1
  JOIN (SELECT m2.conversation_id, MAX(m2.date) AS date
          FROM Message AS m2
         WHERE m2.from = 100 OR m2.to = 100
         GROUP BY m2.conversation_id
       ) AS m3
    ON m1.conversation_id = m3.conversation_id AND m1.date = m3.date
 ORDER BY m1.date DESC;

Upvotes: 2

Related Questions