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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>
>
>