Correct sql/hql query (aggregate in where clause)

Question

I want to do query as below. Query is wrong but describes my intentions.

SELECT name, dateTime, data
FROM Record
WHERE dateTime = MAX(dateTime)

Update: Ok. The query describes intentions not quite good. My bad.

I want to select latest record for each person.

Answer 1

Try This:

SELECT name, dateTime, data
FROM Record 
WHERE dateTime = SELECT MAX(dateTime) FROM Record

You could also write it using an inner join:

SELECT R.name, R.dateTime, R.data
FROM Record R
  INNER JOIN (SELECT MAX(dateTime) FROM Record) RMax ON R.dateTime = RMax.dateTime

Which is the same but written from a different perspective

SELECT R.name, R.dateTime, R.data
FROM Record R,
     (SELECT MAX(dateTime) FROM Record) RMax
WHERE R.dateTime = RMax.dateTime