Myujth

I am looking for the equivalent of this SQL query in JPQL

SELECT l.*
FROM product_update l
 INNER JOIN (
 SELECT
 ID_PRODUCT,
 MAX(ID) AS maxID
 FROM product_update
 GROUP BY ID_PRODUCT
 ) groupel ON l.ID_PRODUCT = groupel.ID_PRODUCT
 AND l.ID = groupel.maxID
ORDER BY ID DESC 

My java class

public class ProductUpdate extends BaseEntity 

private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "PRODUCT_UPDATE_SEQUENCE")
@Column(name = "ID")
private Long id;

@JoinColumn(name = "ID_PRODUCT")
@ManyToOne()
private Product product;


@JoinColumn(name = "ID_VERSION")
@ManyToOne()
private Versioning versioning;

indeed, a product may have several versions, but the system just displays the product with the max (id)
Example:

1-Product 1 --- Version 1 
2-Product 2 --- Version 1
3-Product 3 --- Version 1
4-Product 1 --- Version 2
5-Product 3 --- Version 2
6-Product 3 --- Version 3

that the query just comes out

2-Product 2 --- Version 1
4-Product 1 --- Version 2
6-Product 3 --- Version 3

Thank.

I finally got the good query,

select pu from ProductUpdate pu where pu.id in (select MAX(pUd.id) from ProductUpdate pUd group by pUd.product.id) ORDER BY pu.id DESC. 

I just use this post JPA CriteriaBuilder for join in subquery to perform my answer.

1. Using a JPA subquery only positions in the WHERE, hence the Netbeans warning


2. INNER JOIN in JPA doesn't accept a subquery.