What is the equivalent HQL query for this SQL query (fetch max value from Inner Join)

Question

I've worked my way to this SQL query (tested and working as expected):

SELECT c.carId, p.color as currentColor
FROM Car c
Inner join Paint p
ON( p.PaintId In (Select max(p2.PaintId) From Paint p2 where p2.carId = c.carId) )

What is the equivalent HQL query ? (Suppose that Class names and properties are same with the DB Table/columns)

Please note that using 'Order by paintId' and setMaxResults is not a option. I need to fetch the currentColor inside the query, as I need to use this in the WHERE statement later

---

Additional info

I have a DB with 2 entities : Car and Paint, where a Car can have one or many Paints.

e.g. this query will fetch the most recent paint for car with carId = 15

Select max(p2.PaintId) From Paint p2 where p2.carId = 15

I'm trying to create a query where I can filter cars by their most recent paint (max paintId). The desired output should be instances of Car Class. one instance per row, so the HQL statement should be like

Select distict c 
From Car c
...

Answer 1

Since it is an inner join and you do not use p2 in the select clause, you can move the subquery to the where clause.

SELECT c.carId, s.color as currentColor
FROM 
  Car c,
  Paint p
WHERE
  p.PaintId In (
    Select max(p2.PaintId) 
    From Paint p2 
    where p2.carId = c.carId)

(by the way, s doesn't exist in your query.)

---

Edit: after reading your additional info:

If the car can only have up to two paints, I wonder why you don't just load it to memory and do it the object oriented way:

var car = session.Query...
var carsLastPaint = oneCar.Paints.Last();

You can use the special function maxindex and access the last value in the Paints list:

select c as car, p as lastPaint
from Car c join c.Paints p
where 
  p = c.Paints[maxindex(c.Paints)] 
  and c.id = :carId

Look here at the Hibernate documentation for more great functions :-)