On Fri, 20 Aug 2021, Francisco Olarte wrote:
Is your next_contact really dependent on the contact record (
person+contact_date? ).
Francisco,
Yes.
I mean, your tables seem CRM like. You stated next_contact for old
contacts is not interesting. It seems next_contact is associated just
by a pe
Rich:
On Thu, Aug 19, 2021 at 6:59 PM Rich Shepard wrote:
> On Thu, 19 Aug 2021, Rob Sargent wrote:
> > Yeah, but my quibble is the the table you described up-thread. Your
> > contact table contains next_contact? I think that column should be
> > normalized out.
> Why should I have a separate tab
On 8/19/21 3:07 PM, Rich Shepard wrote:
On Thu, 19 Aug 2021, David G. Johnston wrote:
David,
I'm not at all surprised as I use postgres infrenquently. Once I have
queries producing results I need for my business tracking or client data I
just use them. I'm neither a professional DBA nor data
On Thu, 19 Aug 2021, David G. Johnston wrote:
Well, in this case I suspect you had made a different mistake which caused
the error message (probably the max(c.next_contact)) but instead of
solving the original problem (removing the max(...)) you decided that two
wrongs (adding or extending a gro
On Thu, Aug 19, 2021 at 2:52 PM Rich Shepard
wrote:
> On Thu, 19 Aug 2021, David G. Johnston wrote:
>
> > I thought you said (p.person_nbr, c.contact_date) is already unique?
>
> Yes, that's the PK for the contacts table. I'm still unsure what needs to
> be
> explicitly included in a query. Quite
On Thu, 19 Aug 2021, David G. Johnston wrote:
I thought you said (p.person_nbr, c.contact_date) is already unique?
David,
Yes, that's the PK for the contacts table. I'm still unsure what needs to be
explicitly included in a query. Quite often I leave out a column and
postgres tells me it need
On Thu, Aug 19, 2021 at 12:34 PM Rich Shepard
wrote:
> group by p.person_nbr, c.contact_date
>
I thought you said (p.person_nbr, c.contact_date) is already unique?
David J.
On Thu, 19 Aug 2021, David G. Johnston wrote:
Yeah, you wrote two from clauses…
David,
Mea culpa! I did. Got that fixed.
Now, this query:
---
Select distinct on (p.person_nbr) p.person_nbr,
c.contact_date, max(c.next_contact) as next_contac from contacts,
people as p, contacts as c
where
On 8/19/21 10:17 AM, Rich Shepard wrote:
On Thu, 19 Aug 2021, Adrian Klaver wrote:
So take David Johnston's query:
Select distinct on (person_nbr) ….. order by person_nbr, contact_date
desc;
Adrian,
contact_date --
2021-08-17
2019-05-14
2019-05-15
2021-08-17
2018-04-05
On Thursday, August 19, 2021, Rich Shepard wrote:
> On Thu, 19 Aug 2021, Adrian Klaver wrote:
>
> So take David Johnston's query:
>> Select distinct on (person_nbr) ….. order by person_nbr, contact_date
>> desc;
>>
>
> Adrian,
>
> contact_date --
> 2021-08-17
What’s your point?
> On Aug 19, 2021, at 11:20 AM, Rich Shepard wrote:
>
> On Thu, 19 Aug 2021, Rob Sargent wrote:
>
>> sorry, wasn’t clear: person_nbr, next_contact
>> On the premise that there can only be one next date.
>
> Rob,
>
> I wasn't sufficiently clear. The contacts table has a row for each
> (perso
On Thu, 19 Aug 2021, Rob Sargent wrote:
sorry, wasn’t clear: person_nbr, next_contact
On the premise that there can only be one next date.
Rob,
I wasn't sufficiently clear. The contacts table has a row for each
(person_nbr, contact_date). For each row there's also either a next_contact
date o
On Thu, 19 Aug 2021, Adrian Klaver wrote:
So take David Johnston's query:
Select distinct on (person_nbr) ….. order by person_nbr, contact_date desc;
Adrian,
contact_date
--
2021-08-17
2019-05-14
2019-05-15
2021-08-17
2018-04-05
2021-08-17
2018-04-05
2021-07-23
2019-04-
> On Aug 19, 2021, at 10:59 AM, Rich Shepard wrote:
>
> On Thu, 19 Aug 2021, Rob Sargent wrote:
>
>> Yeah, but my quibble is the the table you described up-thread. Your
>> contact table contains next_contact? I think that column should be
>> normalized out.
>
> Rob,
>
> Why should I have a
On Thu, 19 Aug 2021, Rob Sargent wrote:
Yeah, but my quibble is the the table you described up-thread. Your
contact table contains next_contact? I think that column should be
normalized out.
Rob,
Why should I have a separate table with one column: next_contact? The
next_contact date is associ
On 8/19/21 9:06 AM, Rich Shepard wrote:
On Thu, 19 Aug 2021, Adrian Klaver wrote:
Alright now I am confused. You keep referring to contact_date, yet the
query is referring to next_contact. Are they the same thing, different
things or other?
Adrian,
The table has 5 columns: person_nbr, contac
> On Aug 19, 2021, at 10:31 AM, Rich Shepard wrote:
>
> On Thu, 19 Aug 2021, Rob Sargent wrote:
>
>> Did you try David J’s suggestion? or maybe
>
> Rob,
>
> Yes.
>
>> select person_nbr, max(next_contact) group by person_nbr where
>> next_contact < now();
>
>> A table with person_nbr (pk),
On Thu, 19 Aug 2021, Rob Sargent wrote:
Did you try David J’s suggestion? or maybe
Rob,
Yes.
select person_nbr, max(next_contact) group by person_nbr where
next_contact < now();
A table with person_nbr (pk), next_contact would make this much easier.
Seems to me a person can only have one
> On Aug 19, 2021, at 10:06 AM, Rich Shepard wrote:
>
> On Thu, 19 Aug 2021, Adrian Klaver wrote:
>
>> Alright now I am confused. You keep referring to contact_date, yet the
>> query is referring to next_contact. Are they the same thing, different
>> things or other?
>
> Adrian,
>
> The tabl
On Thu, 19 Aug 2021, Adrian Klaver wrote:
Alright now I am confused. You keep referring to contact_date, yet the
query is referring to next_contact. Are they the same thing, different
things or other?
Adrian,
The table has 5 columns: person_nbr, contact_date, contact_type, notes, and
next_con
On 8/19/21 8:39 AM, Rich Shepard wrote:
On Thu, 19 Aug 2021, Tom Lane wrote:
The best way is usually like
select * from mytable order by contact_date desc limit 1;
If you have an index on contact_date this should work very well indeed.
tom,
I added an index on contact_date and the query r
On Thu, 19 Aug 2021, Tom Lane wrote:
The best way is usually like
select * from mytable order by contact_date desc limit 1;
If you have an index on contact_date this should work very well indeed.
tom,
I added an index on contact_date and the query returned only one row. Huh!
Not what I exp
On Thursday, August 19, 2021, Rich Shepard wrote:
> On Thu, 19 Aug 2021, Rich Shepard wrote:
>
> Please point me to the proper place in the docs where I can learn how to do
>> this.
>>
>
> If I use DISTINCT ON would this produce the most recent date for each
> person_nbr?
>
Yes, I mis-read your
On Thu, 19 Aug 2021, Tom Lane wrote:
The best way is usually like
select * from mytable order by contact_date desc limit 1;
If you have an index on contact_date this should work very well indeed.
Tom,
I don't have an index on that table. I'll add one and try you suggestion.
Thanks,
Ric
On Thu, 19 Aug 2021, David G. Johnston wrote:
Select distinct on (person_nbr) ….. order by person_nbr, contact_date
desc;
David,
Please clarify: would this produce the most recent contact_date for each
person_nbr? The manual reads that two rows (e.g., for the same person_nbr)
are considered d
On 8/19/21 7:37 AM, Rich Shepard wrote:
I have a table of contacts (PK is the person_nbr and contact_date) and I
want to select only the row with the latest (most recent) contact_date. The
Comparison Date/Time sections in the postgres 12 doc doesn't appear to have
what I want, and when I try to u
Rich Shepard writes:
> I have a table of contacts (PK is the person_nbr and contact_date) and I
> want to select only the row with the latest (most recent) contact_date. The
> Comparison Date/Time sections in the postgres 12 doc doesn't appear to have
> what I want, and when I try to use the max()
On Thu, 19 Aug 2021, Rich Shepard wrote:
Please point me to the proper place in the docs where I can learn how to do
this.
If I use DISTINCT ON would this produce the most recent date for each
person_nbr?
Rich
On Thursday, August 19, 2021, Rich Shepard wrote:
> I have a table of contacts (PK is the person_nbr and contact_date)
>
Select distinct on (person_nbr) ….. order by person_nbr, contact_date desc;
David J.
I have a table of contacts (PK is the person_nbr and contact_date) and I
want to select only the row with the latest (most recent) contact_date. The
Comparison Date/Time sections in the postgres 12 doc doesn't appear to have
what I want, and when I try to use the max() aggregate function it throws
30 matches
Mail list logo
