Re: Hibernate Query Question - Get most recent unique rows

Julie Robinson <julierobinson@xxxxxxxxxxx> · Fri, 24 Mar 2006 12:13:24 -0600

OK.  The following how to do this in SQL.  How does this convert into HQL?

select distinct on (qualitycontrolrange) qualitycontrolrange, date, id
from quality_control_reset
order by qualitycontrolrange, date desc;

Julie Robinson wrote:
Given the two classes at the bottom of this email, I'm having trouble 
coming up with a Hibernate query statement that returns a list 
representing all rows in the quality_control_reset table where there is 
only one row for the most recent quality_control_range.  Help?

Example:

In table quality_control_reset, there are the following rows:

id   |   date        |   qualitycontrolrange
---------------------------------------------
1    |  02/23/2006   |   20
2    |  02/23/2006   |   6
3    |  02/28/2006   |   18
4    |  03/01/2006   |   18
5    |  03/23/2006   |   12
6    |  03/23/2006   |   20

I want the results of the following from the query:

id   |   date        |   qualitycontrolrange
---------------------------------------------
2    |  02/23/2006   |   6
4    |  03/01/2006   |   18
5    |  03/23/2006   |   12
6    |  03/23/2006   |   20

QualityControlReset.java:

package test;

import java.util.Date;

/**
 * @hibernate.class table="quality_control_reset"
 */
public class QualityControlReset {
   private Date date;
   private QualityControlRange qualityControlRange;

   public QualityControlReset() {
      // noop
   }

   /**
    * @hibernate.id generator-class="native" column="id"
    */
   public Long getId() {
      return id;
   }

   public void setId(Long long1) {
      id = long1;
   }

   /**
    * @hibernate.property not-null="true"
    */
   public Date getDate() {
      return date;
   }

   public void setDate(Date date) {
      this.date = date;
   }

   /**
    * @hibernate.many-to-one class="test.QualityControlRange" 
cascade="none" not-null="true"
    */
   public QualityControlRange getQualityControlRange() {
      return qualityControlRange;
   }

   public void setQualityControlRange(QualityControlRange 
qualityControlRange) {
      this.qualityControlRange = qualityControlRange;
   }
}

QualityControlRange.java:

package test;

/**
 * @hibernate.class table="quality_control_range"
 */
public class QualityControlRange {
   private String code;

   public QualityControlRange() {
   }

   /**
    * @hibernate.id generator-class="native" column="id"
    */
   public Long getId() {
      return id;
   }

   public void setId(Long long1) {
      id = long1;
   }

   /**
    * @hibernate.property
    */
   public String getCode() {
      return code;
   }

   public void setCode(String code) {
      this.code = code;
   }
}

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
      message can get through to the mailing list cleanly