Postgres Pro
Downloads
Home   >   mailing lists

Thread: Hibernate Query Question - Get most recent unique rows

Hibernate Query Question - Get most recent unique rows

From
Julie Robinson
Date:
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   |   timestamp   |   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   |   timestamp   |   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;
    }
}

Re: Hibernate Query Question - Get most recent unique rows

From
Julie Robinson
Date:
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@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>
>
>

Re: Hibernate Query Question - Get most recent unique rows

From
"Jim C. Nasby"
Date:
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