Re: SQL QUESTION
why you are making self join ?
SELECT a.pkey, a.pname, a.rx, MAX(a.fdate)
FROM mytable a
GROUP BY a.pkey,a.pname,a.rx
ORDER BY a.pname;
Isn't this what you want ? (or have misunderstood your requirement ?)
Alkesh Vipani.
----- Original Message -----
From: "Michael J. Prichard" <chakraboy@mex.excite.com>
To: <informix-list@iiug.org>
Sent: Monday, February 14, 2000 3:38 PM
Subject: SQL QUESTION
> Hello,
>
> I have a table which keeps a large number of records that may have the
same
> info but has a few different values including dates, in various fields
> (this is a basic overview). Anyway, I wish to pick out the records with
the
> highest date in a certain field.
>
> Example TABLE mytable:
>
> pkey
> pname
> rx
> fdate
>
> I want to pick individual records with the highest fdate.
>
> So I write:
>
> SELECT a.pkey, b.pname, a.rx, MAX(b.fdate)
> FROM mytable a, mytable b
> WHERE a.rx=b.rx
> GROUP BY a.pkey,a.pname,a.rx
> ORDER BY a.pname>
> Now this seems to work but my database is running out of /tmp space to
> perform the query. Is there anything I could change or maybe rewrite more
> efficiently as for this operation to work? We are talking about millions
of
> initial records.
>
>
Hello,
You both are right. I actually thought about that earlier and asked my self
the same question. A join is definitely not needed.
Thanks,
Michael
Alkesh Vipani <alkesh.vipani@wcom.com> wrote in message
news:88c1n0$oeo$1@news.xmission.com...
>
> why you are making self join ?
> SELECT a.pkey, a.pname, a.rx, MAX(a.fdate)
> FROM mytable a
> GROUP BY a.pkey,a.pname,a.rx
> ORDER BY a.pname;>
> Isn't this what you want ? (or have misunderstood your requirement ?)
> Alkesh Vipani.
>
> ----- Original Message -----
> From: "Michael J. Prichard" <chakraboy@mex.excite.com>
> To: <informix-list@iiug.org>
> Sent: Monday, February 14, 2000 3:38 PM
> Subject: SQL QUESTION
>
>
> > Hello,
> >
> > I have a table which keeps a large number of records that may have the
> same
> > info but has a few different values including dates, in various fields
> > (this is a basic overview). Anyway, I wish to pick out the records with
> the
> > highest date in a certain field.
> >
> > Example TABLE mytable:
> >
> > pkey
> > pname
> > rx
> > fdate
> >
> > I want to pick individual records with the highest fdate.
> >
> > So I write:
> >
> > SELECT a.pkey, b.pname, a.rx, MAX(b.fdate)
> > FROM mytable a, mytable b
> > WHERE a.rx=b.rx
> > GROUP BY a.pkey,a.pname,a.rx
> > ORDER BY a.pname> >
> > Now this seems to work but my database is running out of /tmp space to
> > perform the query. Is there anything I could change or maybe rewrite
more
> > efficiently as for this operation to work? We are talking about
millions
> of
> > initial records.
> >
> >
>