Re: Hibernate Query Question - Get most recent unique rows

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
>
>
>
>