Search code examples
sqlhql

Transforming SQL to HQL

my table is the following:

Id   Timestamp            name_id     testobject_id 
----------------------------------------------------
2  | 2017-12-15 18:36:46 | 1        |  1
3  | 2017-12-15 18:36:46 | 2        |  1
1  | 2016-01-01 00:00:00 | 1        |  1
4  | 2017-12-15 18:36:46 | 1        |  2
5  | 2017-12-15 18:36:46 | 2        |  2

I want the last record of each group (name_id). But i only want the results where testobject_id = 1

This SQL works just fine:

SELECT *
    FROM checkresult
    WHERE test_object_id = 4
    AND timestamp IN (
        SELECT MAX(timestamp)
        FROM checkresult
        WHERE test_object_id = 4    
        GROUP BY name_id
    );

My HQL does not work.

FROM checkresult as cr 
 WHERE cr.testobject = :testobject 
  AND cr.timestamp IN ( 
   MAX(cr.timestamp) 
    FROM checkresult as result WHERE result.testobject = :testobject 
     GROUP BY result.checkName)

I am thankful for every help.

Desired Output:

Id   Timestamp            name_id     testobject_id 
----------------------------------------------------
2  | 2017-12-15 18:36:46 | 1        |  1
3  | 2017-12-15 18:36:46 | 2        |  1
Solution

  • You have written MAX(cr.timestamp) in your second SQL statement. But it should be

    MAX(result.timestamp) 
FROM checkresult as result WHERE result.testobject = :testobject 
GROUP BY result.checkName

    Also what is your error message?