Help with HQL query (group by, order by)

I have problem writing HQL to display distinct applicationId with the latest application (newest createDate) for the following data in the table.

+---------------+-------------+----------+---------------------+
| applicationId | firstName   | lastName | createDate          |
+---------------+-------------+----------+---------------------+
|             1 | Mark        | Johnson  | 2010-05-03 00:00:00 |
|             3 | Henry       | Jordan   | 2010-05-03 00:00:00 |
|             5 | Cindy Spahn | Wilson   | 2010-05-03 00:00:00 | 
|             5 | Cindy Spahn | Wilson   | 2010-05-04 00:00:00 |
|             5 | Cindy Spahn | Wilson   | 2010-05-05 00:00:00 |
+---------------+-------------+----------+---------------------+
5 rows in set (0.00 sec)

Below is the result that I'm looking for:

+---------------+-------------+----------+---------------------+
| applicationId | firstName   | lastName | createDate          |
+---------------+-------------+----------+---------------------+
|             1 | Mark        | Johnson  | 2010-05-03 00:00:00 |
|             3 | He开发者_如何学运维nry       | Jordan   | 2010-05-03 00:00:00 |
|             5 | Cindy Spahn | Wilson   | 2010-05-05 00:00:00 |
+---------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)

Entities are as follow:

@Entity
@Table(name = "application")
public class Application {
    private long applicationId;
    private String firstName;
    private String lastName;
    private List<ApplicationHistory> applicationHistoryList;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public long getApplicationId() {
        return applicationId;
    }

    @OneToMany(mappedBy = "application", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    public List<ApplicationHistory> getApplicationHistoryList() {
        return applicationHistoryList;
    }
    // getter() and setter()
}

and:

@Entity
@Table(name = "applicationHistory")
public class ApplicationHistory {
    private Application application;
    private final Timestamp createDate = new Timestamp(System.currentTimeMillis());

    @ManyToOne
    @JoinColumn(name = "applicationId", insertable = false, updatable = false)
    public Application getApplication() {
        return application;
    }

    @Id
    @Column(columnDefinition = "timestamp default current_timestamp")
    public Timestamp getCreateDate() {
        return createDate;
    }
}

You can use below query:

select a, h.createDate  from Application as a  join a.applicationHistoryList as h where (a.applicationId, h.createDate) in( SELECT application.applicationId, max(createDate) FROM ApplicationHistory  group by application.applicationId) 

eg:

Query q = em.createQuery("select a, h.createDate  from Application as a  join a.applicationHistoryList as h where (a.applicationId, h.createDate) in( SELECT application.applicationId, max(createDate) FROM ApplicationHistory  group by application.applicationId) ");

        List list = q.getResultList();

        for (Iterator iterator = list.iterator(); iterator.hasNext();) {
            Object obj[] = (Object[])iterator.next();
            Application a =  (Application) obj[0];

            System.out.println("ApplicationId="+a.getApplicationId() );
            System.out.println("CreateDate="+obj[1] );

        }

Try to do it with group by clause:

select ah from ApplicationHistory ah group by ah.applicationId order by ah.createDate desc