after hours I don't seem to find a way to achieve this, I have 2 entities:
public class Price{
@Id
int id;
@Temporal(TemporalType.DATE)
private Date dtComu;
private String descCarb;
private Double price;
//bi-directional many-to-one association to Distributor
@ManyToOne
@JoinColumn(name="idImpiant")
private Distributor distributor;
AND
public class Distributor{
@Id
private int idImpiant;
private String province;
//bi-directional many-to-one association to Price
@OneToMany(mappedBy="distributor")
private List<Price> prices;
What I need to do seems pretty simple, for each distributor, get the latest price. What i thought was to get a list of Prices(each price with have a distributor), order by date and group by distributor. My query is:
SELECT e FROM Price e JOIN e.distributor d WHERE e.descCarb like '%Diesel%' group by e.distributor order by e.dtComu desc
The error i get is:
SELECT list is not in GROUP BY clause and contains nonaggregated column 'price0_.id' which is not functionally dependent on columns in GROUP BY clause
is there another way i haven't thought to achieve this?
The error you see is stemming from that it is not clear which entity you want per distributor group. Here is one way to do this using HQL:
select e FROM Price e JOIN e.distributor d
where e.descCarb like '%Diesel%' and
e.dtComu = (select max(ee.dtComu) from Price ee where ee.distributor = e.distributor)
This uses a correlated subquery to check that the matching Price
entity is the latest one for each distributor.