JPQL Query to select row with minimum value

Question

Similar to SQL query to select distinct row with minimum value and Select rows with minimum date but I am asking specifically for JPQL because the examples provided return * but I would want it to be the same object.

Say I have the query like this.

from BillingTargetDiscountAmount g 
  where g.billingTarget = :billingTarget 
   and g.discountSource = :discountSource 
   and g.startDate <= :date

I also have a composite key of

@Data
public class BillingTargetDiscountAmountPK implements Serializable {
    @ToString.Exclude
    private BillingTarget billingTarget;

    private String discountSource;

    private LocalDate startDate;

    @ToString.Include
    private Integer billingTargetID() {

        return billingTarget.getId();

    }

}

My guess would be something like though I am not sure what to do with the specific synax

select * [??? BillingTargetDiscountAmount ] from (
  select g.billingTarget, g.discountSource, min(g.startDate), other fields
  from BillingTargetDiscountAmount g 
    where g.billingTarget = :billingTarget 
     and g.discountSource = :discountSource 
     and g.startDate <= :date
)

score 0 · Answer 1 · answered Feb 12 '22 at 08:20

Since I only needed to do one column for the min value. I did a subselect and just repeated the parameters

from BillingTargetDiscountAmount btda 
where btda.billingTarget = :billingTarget and 
      btda.discountSource = :discountSource and 
      btda.startDate = (
        select min(g.startDate)
          from BillingTargetDiscountAmount g 
          where g.billingTarget = :billingTarget 
            and g.discountSource = :discountSource 
            and g.startDate <= :date
      )

JPQL Query to select row with minimum value

1 Answers1