Re: Subquery to select max(date) value - Mailing list pgsql-general

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 join. I'll trace what's happening at each step
> and learn where to specify no nulls.

create table people(person_id integer, desc_fld varchar);
create table activities(person_id integer, next_contact date);

insert into people values (1, 'contacted'), (2, 'never contacted'), (3, 
'out of range'), (4, 'contacted');

insert into activities values (1, '01/31/19'), (3, '11/01/18'), (4, 
'02/02/19');

No activities record for person_id =2, activities data set to NULL:

SELECT  * FROM people AS p LEFT JOIN activities AS a ON p.person_id = 
a.person_id;
  person_id |    desc_fld     | person_id | next_contact
-----------+-----------------+-----------+--------------
          1 | contacted       |         1 | 2019-01-31
          2 | never contacted |      NULL | NULL
          3 | out of range    |         3 | 2018-11-01
          4 | contacted       |         4 | 2019-02-02

Close to your last posted query. person_id 2 and 3 have NULL values for
activities data as there is no record for 2 and 3 is out of the date range.:

  select
    p.person_id,
    p.desc_fld,
    a.next_contact
from
    people as p
    LEFT JOIN (
        SELECT
            DISTINCT ON (person_id)
            person_id,
            next_contact
        FROM
             activities a
        WHERE
            a.next_contact <= 'today' and a.next_contact > '2018-12-31' and
            a.next_contact is not null
    ) a USING (person_id)
;

  person_id |    desc_fld     | next_contact
-----------+-----------------+--------------
          1 | contacted       | 2019-01-31
          2 | never contacted | NULL
          3 | out of range    | NULL
          4 | contacted       | 2019-02-02
(4 rows)

What I think you want:

select
    p.person_id,
    p.desc_fld,
    a.next_contact
from
    people as p
    LEFT JOIN (
        SELECT
            DISTINCT ON (person_id)
            person_id,
            next_contact
        FROM
             activities a
        WHERE
            a.next_contact <= 'today' and a.next_contact > '2018-12-31' and
            a.next_contact is not null
    ) a USING (person_id)
WHERE next_contact is not null;

  person_id | desc_fld  | next_contact
-----------+-----------+--------------
          1 | contacted | 2019-01-31
          4 | contacted | 2019-02-02





> 
> Thanks,
> 
> Rich
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com