pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmax question
4+ messages / 4 participants
[nested] [flat]
* max question
@ 2005-04-12 17:24 A. R. Van Hook <hook@lake-lotawana.mo.us>
2005-04-13 04:46 ` Re: max question Tom Lane <tgl@sss.pgh.pa.us>
2005-04-13 05:02 ` Re: max question Dinesh Pandey <dpandey@secf.com>
2005-04-13 13:20 ` Re: max question Bruno Wolff III <bruno@wolff.to>
0 siblings, 3 replies; 4+ messages in thread
From: A. R. Van Hook @ 2005-04-12 17:24 UTC (permalink / raw)
To: pgsql-sql
I have the following in a table:
oid | citkey | contby | contdate | abcontinue | ccdate
---------+------------+----------+------------+------------+------------
5774835 | 04-0594703 | | | |
5775325 | 04-0594703 | Attorney | 04/06/2005 | 6 | 03/07/2005
5776060 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005
5776067 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005
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"
oid | citkey | contby | contdate | abcontinue | ccdate
---------+------------+----------+------------+------------+------------
5776067 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005
5775325 | 04-0594703 | Attorney | 04/06/2005 | 6 | 03/07/2005
5776060 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005
(3 rows)
thanks
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: max question
2005-04-12 17:24 max question A. R. Van Hook <hook@lake-lotawana.mo.us>
@ 2005-04-13 04:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2005-04-13 04:46 UTC (permalink / raw)
To: A. R. Van Hook <hook@lake-lotawana.mo.us>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: max question
2005-04-12 17:24 max question A. R. Van Hook <hook@lake-lotawana.mo.us>
@ 2005-04-13 05:02 ` Dinesh Pandey <dpandey@secf.com>
2 siblings, 0 replies; 4+ messages in thread
From: Dinesh Pandey @ 2005-04-13 05:02 UTC (permalink / raw)
To: 'A. R. Van Hook' <hook@lake-lotawana.mo.us>; pgsql-sql
Try
"select oid,* from ccontinue where citkey ='04-0594703' group by
oid,citkey,contby,contdate,abcontinue,ccdate having contdate= max(contdate)"
Thanks
Dinesh Pandey
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of A. R. Van Hook
Sent: Tuesday, April 12, 2005 10:54 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] max question
I have the following in a table:
oid | citkey | contby | contdate | abcontinue | ccdate
---------+------------+----------+------------+------------+------------
5774835 | 04-0594703 | | | |
5775325 | 04-0594703 | Attorney | 04/06/2005 | 6 | 03/07/2005
5776060 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005
5776067 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005
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"
oid | citkey | contby | contdate | abcontinue | ccdate
---------+------------+----------+------------+------------+------------
5776067 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005
5775325 | 04-0594703 | Attorney | 04/06/2005 | 6 | 03/07/2005
5776060 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005
(3 rows)
thanks
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: max question
2005-04-12 17:24 max question A. R. Van Hook <hook@lake-lotawana.mo.us>
@ 2005-04-13 13:20 ` Bruno Wolff III <bruno@wolff.to>
2 siblings, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2005-04-13 13:20 UTC (permalink / raw)
To: A. R. Van Hook <hook@lake-lotawana.mo.us>; +Cc: pgsql-sql
On Tue, Apr 12, 2005 at 12:24:21 -0500,
"A. R. Van Hook" <hook@lake-lotawana.mo.us> wrote:
> I have the following in a table:
> oid | citkey | contby | contdate | abcontinue | ccdate
> ---------+------------+----------+------------+------------+------------
> 5774835 | 04-0594703 | | | |
> 5775325 | 04-0594703 | Attorney | 04/06/2005 | 6 | 03/07/2005
> 5776060 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005
> 5776067 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005
>
> 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"
If you group by oid you are going to get a separate group for each row.
You haven't told us what you actually expect for output, so I don't
have specific advise for a query. However, you might find the Postgres
extension DISTINCT ON useful, depending on exactly what you want.
> oid | citkey | contby | contdate | abcontinue | ccdate
> ---------+------------+----------+------------+------------+------------
> 5776067 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005
> 5775325 | 04-0594703 | Attorney | 04/06/2005 | 6 | 03/07/2005
> 5776060 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005
> (3 rows)
>
> thanks
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-04-13 13:20 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-12 17:24 max question A. R. Van Hook <hook@lake-lotawana.mo.us>
2005-04-13 04:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-13 05:02 ` Dinesh Pandey <dpandey@secf.com>
2005-04-13 13:20 ` Bruno Wolff III <bruno@wolff.to>
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