Re: Hibernate Query Question - Get most recent unique rows

On Fri, Mar 24, 2006 at 12:13:24PM -0600, Julie Robinson wrote:
> 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;

That won't do what you described below. I think what you want is this:

SELECT quality_control_range, max(date) FROM quality_control_reset GROUP BY
qualitycontrolrange;

If you need id, just wrap the above in a subquery and join against it.

> 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@postgresql.org so that your
> >      message can get through to the mailing list cleanly
> >
> >
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461