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(at)postgresql(dot)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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461