Hi. I need some help to map a SQL query for a 'greatest-n-per-group' problem to exposed DSL. It uses several JOIN with some subselect, and I have no idea where to start to translate this to the DSL. Here is the raw query. Any hints will be appreciated 🙂

SELECT p.id, p.title
FROM Publication p
JOIN ( 
	 SELECT psc.publicationId, psc.publicationStateId ,max(psc.id) 
	 FROM PublicationStateChange  psc
	 GROUP BY psc.publicationId
	) lastpsc ON p.id = lastpsc.publicationId and lastpsc.publicationStateId = ps.id
JOIN PublicationState ps ON ps.id = lastpsc.publicationStateId

WHERE ps.tag = "new"
;

I’m not sure what I understand your query well, but something like this should work.

@tapac Thanks I read the above link content yesterday for a long time before deciding posting here. I was not able to translate my SQL to DSL reading those examples. I believe the doc should contains more "cookbook" examples like that. Thanks for the solution you proposed, i'll test it today.