Help with HQL query (group by, order by)

Question

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 | Henry       | 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;
    }
}

Answer 1

Try to do it with group by clause:

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