On Fri, 29 Mar 2019, Andrew Gierth wrote:
That query seems correct assuming you want the result in descending order
of next_contact. How did the actual result differ from your expectation?
Andrew,
User error: I had a couple of date typos (2018 rather than 2019) and was
thinking of generating
On Thu, 28 Mar 2019, Ken Tanzer wrote:
Really? Is your next_contact field a date field and not a text field? What
order does it come out in?
(And I assume you saw David J's correction to the misinformation I offered.)
Ken,
Mea culpa. the next_contact column is a date, I did apply David's
cor
On Thu, 28 Mar 2019, David G. Johnston wrote:
It returns something at least. If you put the output you get into a
spreadsheet are you able to manually sort it the way you desire?
David,
Probably, but that's not practical for the application.
Thanks,
Rich
> "Rich" == Rich Shepard writes:
Rich> Tried this and did not do it correctly. Should there be two
Rich> 'order by', one in the sub-query, the other in the top-level
Rich> query?
Yes.
Rich> This does not return the desired order:
Rich> select p.person_id, p.lname, p.fname, p.direct_ph
On Thu, Mar 28, 2019 at 4:21 PM Rich Shepard
wrote:
> This does not return the
> desired order:
>
It returns something at least. If you put the output you get into a
spreadsheet are you able to manually sort it the way you desire?
David J.
On Thu, Mar 28, 2019 at 4:14 PM Rich Shepard
wrote:
> On Thu, 28 Mar 2019, Ken Tanzer wrote:
>
> > You need the ORDER BY in the outer join.
>
> Ken,
>
> I thought so. But, ...
>
> > (And I don't think the one inside the lateral join is doing you any
> good).
> > Try:
> > ...
> > a.ne
On Thu, 28 Mar 2019, David G. Johnston wrote:
Yes, if you join the result on an ordered subquery to anything you no
longer have a guaranteed order for the combined relation.
David,
This makes sense to me.
select ...
from ...
join ...
cross join lateral ...
-- now add an order by for the top
On Thu, 28 Mar 2019, Ken Tanzer wrote:
You need the ORDER BY in the outer join.
Ken,
I thought so. But, ...
(And I don't think the one inside the lateral join is doing you any good).
Try:
...
a.next_contact is not null
limit 1) sq
order by sq.next_contact
On Thu, Mar 28, 2019 at 4:07 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Thu, Mar 28, 2019 at 4:05 PM Ken Tanzer wrote:
>
>>
>> (And I don't think the one inside the lateral join is doing you any
>>> good). Try:
>>>
>>
>> ...
>> a.next_contact is not null
>>
On Thu, Mar 28, 2019 at 4:05 PM Ken Tanzer wrote:
>
> (And I don't think the one inside the lateral join is doing you any
>> good). Try:
>>
>
> ...
> a.next_contact is not null
> limit 1) sq
> order by sq.next_contact DESC;
>
>
The fact that the subquery h
On Thu, Mar 28, 2019 at 3:59 PM Rich Shepard
wrote:
> select p.person_id, p.lname, p.fname, p.direct_phone, p.active,
> o.org_name, sq.*
> from people as p
> join organizations as o on p.org_id = o.org_id
> cross join
> lateral
> (select a.next_contact
>
On Thu, Mar 28, 2019 at 3:59 PM Rich Shepard
wrote:
>
> After working with this query I modified it slightly to return only the
> next_contact date:
>
> select p.person_id, p.lname, p.fname, p.direct_phone, p.active,
> o.org_name, sq.*
> from people as p
> join organizations as o on p.org_i
On Fri, 15 Feb 2019, Andrew Gierth wrote:
select p.person_id, p.lname, p.fname, p.direct_phone, o.org_name, sq.*
from people as p
join organizations as o on p.organization_id=o.id -- OR WHATEVER
cross join
lateral (select a.next_contact
from activities a
On Fri, 15 Feb 2019, Andrew Gierth wrote:
Rich> I've not before run 'explain' on a query. Would that be
Rich> appropriate here?
Yes.
Andrew,
I'll learn how to use it.
The problem here is that you have no join conditions at all, so the
result set of this query is massive. And you've duplica
> "Rich" == Rich Shepard writes:
Rich> Using LIMIT 1 produces only the first returned row. This
Rich> statement (using max() for next_contact) produces no error
Rich> message, but also no results so I killed the process after 30
Rich> seconds. Without a syntax error for guidance I don't k
On Fri, 15 Feb 2019, Andrew Gierth wrote:
LATERAL (SELECT ...) is syntactically like (SELECT ...) in that it
comes _after_ a "," in the from-clause or after a [LEFT] JOIN keyword.
Andrew,
Yes, the missing ',' made a big difference.
You'd want a condition here that references the "people"
On Fri, 15 Feb 2019, Andrew Gierth wrote:
LATERAL (SELECT ...) is syntactically like (SELECT ...) in that it comes
_after_ a "," in the from-clause or after a [LEFT] JOIN keyword. Don't
think of LATERAL as being a type of join, think of it as qualifying the
(SELECT ...) that follows.
Andrew,
> "Rich" == Rich Shepard writes:
Rich> I found a couple of web pages describing the lateral join yet
Rich> have not correctly applied them. The manual's page did not help
Rich> me get the correct syntax, either. Think I'm close, however:
Rich> select p.person_id, p.lname, p.fname, p.dire
On Wed, 13 Feb 2019, Andrew Gierth wrote:
You want LATERAL.
Andrew, et al,:
I found a couple of web pages describing the lateral join yet have not
correctly applied them. The manual's page did not help me get the correct
syntax, either. Think I'm close, however:
select p.person_id, p.lname,
may be provided by
third parties. Such third parties can access information transmitted to,
processed by and stored on NIWA's IT systems.
From: Rich Shepard
Sent: Thursday, February 14, 2019 12:13
To: pgsql-generallists.postgresql.org
Subject: Re: Subqu
On Wed, 13 Feb 2019, Brent Wood wrote:
I have not really followed this thread but would not a query along the lines of
select * from activity where person_id = n and timestamp = (select
max(timestamp) from activity where person_id = n);
give the required answer ie, always return the latest resul
On Wed, 13 Feb 2019, Adrian Klaver wrote:
Given a sufficiently large date range that may not be true as you may have
contacted a given person multiple times during that range and generated
multiple activities records.
Adrian,
This is true as it has occurred. I want only the most recent activi
On 2/13/19 2:24 PM, Rich Shepard wrote:
On Wed, 13 Feb 2019, Andrew Gierth wrote:
Adrian> Close to your last posted query. person_id 2 and 3 have NULL
Adrian> values for activities data as there is no record for 2 and 3 is
Adrian> out of the date range.:
DISTINCT ON with no matching ORDER BY
On Wed, 13 Feb 2019, Andrew Gierth wrote:
Adrian> Close to your last posted query. person_id 2 and 3 have NULL
Adrian> values for activities data as there is no record for 2 and 3 is
Adrian> out of the date range.:
DISTINCT ON with no matching ORDER BY at the _same_ query level is
non-determi
> "Adrian" == Adrian Klaver writes:
Adrian> Close to your last posted query. person_id 2 and 3 have NULL
Adrian> values for activities data as there is no record for 2 and 3 is
Adrian> out of the date range.:
Adrian> select
Adrian>p.person_id,
Adrian>p.desc_fld,
Adrian>a.
On 2/13/19 7:37 AM, Rich Shepard wrote:
On Wed, 13 Feb 2019, Adrian Klaver wrote:
The LEFT JOIN. There are rows in people for which there no records coming
from the sub-select on activities, so the row is 'padded' with NULL
values
for the missing data.
Adrian,
I assume it's the inner left
On Wed, 13 Feb 2019, Adrian Klaver wrote:
It will work if you use it to filter after the joins are done.
I'll work on finding the proper syntax to do this. Need to do more reading
and trial-and-error testing.
Regards,
Rich
On Wed, Feb 13, 2019, at 9:06 AM, Rich Shepard wrote:
> On Wed, 13 Feb 2019, Adrian Klaver wrote:
>
> > Should have been clearer in my previous post, you can get rid of the nulls
> > by filtering out the entire row.
>
> Adrian,
>
> Thank you. I'm rebuilding the statement from the inside out (whi
On Wed, 13 Feb 2019, Adrian Klaver wrote:
Should have been clearer in my previous post, you can get rid of the nulls
by filtering out the entire row.
Adrian,
Thank you. I'm rebuilding the statement from the inside out (which helps me
learn more SQL in the process). For example,
select * from
On Wed, 13 Feb 2019, Adrian Klaver wrote:
AFAIK there is no inner left join:
https://www.postgresql.org/docs/10/sql-select.html#SQL-FROM
Sigh. There are two LEFT JOINS in the statement. I referred to the SECOND
one as INNER. I correct my message to refer to the second of the two left
joins.
R
On 2/13/19 7:37 AM, Rich Shepard wrote:
On Wed, 13 Feb 2019, Adrian Klaver wrote:
The LEFT JOIN. There are rows in people for which there no records coming
from the sub-select on activities, so the row is 'padded' with NULL
values
for the missing data.
Adrian,
I assume it's the inner left
On 2/13/19 7:37 AM, Rich Shepard wrote:
On Wed, 13 Feb 2019, Adrian Klaver wrote:
The LEFT JOIN. There are rows in people for which there no records coming
from the sub-select on activities, so the row is 'padded' with NULL
values
for the missing data.
Adrian,
I assume it's the inner left
On Wed, 13 Feb 2019, Adrian Klaver wrote:
The LEFT JOIN. There are rows in people for which there no records coming
from the sub-select on activities, so the row is 'padded' with NULL values
for the missing data.
Adrian,
I assume it's the inner left join. I'll trace what's happening at each s
On 2/13/19 6:28 AM, Rich Shepard wrote:
On Tue, 12 Feb 2019, Ken Tanzer wrote:
If that's getting you what you want, then great and more power to
you. It
looks like you'll only get people who have a next_contact in your target
window there. You might also consider something like this...
sele
On Tue, 12 Feb 2019, Ken Tanzer wrote:
If that's getting you what you want, then great and more power to you. It
looks like you'll only get people who have a next_contact in your target
window there. You might also consider something like this...
select
p.person_id,
p.lname,
p.fname,
On Wed, 13 Feb 2019, Andrew Gierth wrote:
Rich> Will probably try DISTINCT ON, too, if that makes it simpler or
Rich> faster.
You want LATERAL.
Andrew,
That's new to me so I'll read about it.
Thanks,
Rich
Am Dienstag, 12. Februar 2019, 23:23:49 CET schrieb Rich Shepard:
> The query is to return the latest next_contact date for each person. Using
> the max() aggregate function and modeling the example of lo_temp on page 13
> of the rel. 10 manual I wrote this statement:
You don't even need a subsele
> "Rich" == Rich Shepard writes:
Rich> Will probably try DISTINCT ON, too, if that makes it simpler or
Rich> faster.
You want LATERAL.
--
Andrew (irc:RhodiumToad)
On Tue, 12 Feb 2019, Ken Tanzer wrote:
If that's getting you what you want, then great and more power to you. It
looks like you'll only get people who have a next_contact in your target
window there. You might also consider something like this...
Ken,
I'll work with your example. This loo
>
>
> Ken,
>
> Yes, cheers indeed. A bit of thinking and re-organizing resulted in a
> working statement that's close to what I want:
>
> select (P.person_id, P.lname, P.fname, P.direct_phone, O.org_name,
> (select max(A.next_contact)))
> from People as P, Organizations as O, Activities as
On Tue, 12 Feb 2019, Ken Tanzer wrote:
select (P.person_id, P.lname, P.fname, P.direct_phone, O.org_name,
(select max(A.next_contact) from Activities as A WHERE
p.person_id=A.person_id)
FROM ...
Ken,
Yes, cheers indeed. A bit of thinking and re-organizing resulted in a
working statement that
On Tue, 12 Feb 2019, Adrian Klaver wrote:
'infinity' is the max date, so this is what you want?
Adrian,
Nope. When I went to make a cup of coffee I realized that I need the other
date constraints (such as IS NOT NULL), too. I'm re-wording the statement to
put everything in the correct order.
On Tue, Feb 12, 2019 at 2:48 PM Rich Shepard
wrote:
> On Tue, 12 Feb 2019, Rich Shepard wrote:
>
> > A.next_contact = (select (max(A.next_contact)) from Activities as A)
>
> Errata:
>
> The parentheses around the max aggregate are not necessary.
>
> A.next_contact now displays at the end of
On 2/12/19 2:48 PM, Rich Shepard wrote:
On Tue, 12 Feb 2019, Rich Shepard wrote:
A.next_contact = (select (max(A.next_contact)) from Activities as A)
Errata:
The parentheses around the max aggregate are not necessary.
A.next_contact now displays at the end of each returned row as 'infi
Hey Rich,
On Tue, Feb 12, 2019 at 4:24 PM Rich Shepard
wrote:
> The query is to return the latest next_contact date for each person. Using
> the max() aggregate function and modeling the example of lo_temp on page 13
> of the rel. 10 manual I wrote this statement:
>
I use DISTINCT ON and ORDER
On Tue, 12 Feb 2019, Rich Shepard wrote:
A.next_contact = (select (max(A.next_contact)) from Activities as A)
Errata:
The parentheses around the max aggregate are not necessary.
A.next_contact now displays at the end of each returned row as 'infinity'.
Rich
On Tue, 12 Feb 2019, Jeff Ross wrote:
Try (select (max(A.next_contact) from A)
Thanks, Jeff.
The syntax accepted by psql is
A.next_contact = (select (max(A.next_contact)) from Activities as A)
but the date is not included in the output.
The revised statement is now:
select (P.person_id, P.ln
On Tue, 12 Feb 2019, David G. Johnston wrote:
You put the open parenthesis after the word select instead of before.
A.next_contact = (SELECT max(A.next_contact) FROM A)
David.
Color me suitably embarrassed.
Thank you,
Rich
On Tue, Feb 12, 2019 at 3:24 PM Rich Shepard wrote:
> psql:next_contact_date.sql:7: ERROR: syntax error at or near "select"
> LINE 4: A.next_contact = select (max(A.next_contact) from A)
> ^
> and I fail to see what I've done incorrectly.
You put the open pa
49 matches
Mail list logo
