In a MySQL DB / Table livedata_tbl has THREE columns: Device_Id (numeric), Stored_At (Timestamp), Status (String).

Which represents Device POJO Class, which has deviceId as Integer, storedAt as Timestamp, status as String.

Possible values for status can only be "Dead" or "Alive".

Records are stored into this database very frequently with varying status with each entry (for each record), and each Device_Id can repeat multiple times in this table.

But the requirement is:

Requirement 1: select unique Device Object based on their individual latest timestamp value.
Requirement 2: How to get count of Status (grouped by status column) from the resultset from above requirement 1?

Example set of records in database:

Device Id Stored_At Status
--------- ---------- -------
1 2009-01-01 10:22:33 Alive
2 2009-01-01 10:22:34 Dead
3 2009-01-01 10:22:35 Alive
1 2009-01-01 10:22:36 Dead
2 2009-01-01 10:22:37 Dead
3 2009-01-01 10:22:38 Dead
3 2009-01-01 10:22:39 Alive
3 2009-01-01 10:22:40 Dead
4 2009-01-01 10:22:41 Dead
1 2009-01-01 10:22:42 Alive
2 2009-01-01 10:22:43 Dead

Expected Output:

Requirement 1 expected result::(Select all records having unique device_id which in turn have latest timestamp on it)

Device Id Stored_At Status
--------- ---------- -------
1 2009-01-01 10:22:42 Alive
2 2009-01-01 10:22:43 Dead
3 2009-01-01 10:22:40 Dead
4 2009-01-01 10:22:41 Dead

Requirement 2 expected result::(Select status and its unique status count)

Status Count
--------- -------
Alive 1
Dead 3

This query has to written in Hibernate in Java.

Any thoughts?