From: Tom Lane <tgl@sss.pgh.pa.us>
To: A. R. Van Hook <hook@lake-lotawana.mo.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: max question
Date: Wed, 13 Apr 2005 00:46:37 -0400
Message-ID: <3273.1113367597@sss.pgh.pa.us> (raw)
In-Reply-To: <425C0445.8020603@lake-lotawana.mo.us>
References: <425C0445.8020603@lake-lotawana.mo.us>
"A. R. Van Hook" <hook@lake-lotawana.mo.us> writes:
> I am trying to pull rows that have the max. contdate. Why does the
> following give more than 2 rows?
> ql "select oid,* from ccontinue where citkey ='04-0594703' group by
> oid,citkey,contby,contdate,abcontinue,ccdate having max(contdate) =
> contdate"
HAVING is going to interpret the max() aggregate separately for each
group ... that is, the above query asks for all the rows that have the
largest contdate within their group. Given that OID is one of the
grouping columns, I'd pretty much expect that to select every single
row in the table, because each row will form its own unique group :-(
What is it you are trying to accomplish here? In particular, what
led to that choice of GROUP BY?
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, hook@lake-lotawana.mo.us
Subject: Re: max question
In-Reply-To: <3273.1113367597@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox