How to get the MAX date in a query?

Question

I have two tables in a Postgres database:

table_1

Name    Address   Height    Weight   Date_Collected
X.        y         z.        p.       8/7/2024
G.        H.        I         J.       8/8/2024
Q         W.        E.        R.       8/9/2024

table_2

Name    Income     Tax    Date_Collected 
X.        y         z.      8/8/2024
A         B         C.      8/8/2024

Trying compare the rows between the two tables and get the names that appear only in table_1 and not in table_2. I am also trying to filter them out by the MAX date collected.

Here is what I have so far but I am getting a syntax error:

SELECT count(DISTINCT "Name")
FROM table_1
WHERE table_1.Height NOT LIKE ANY CONCAT('%', {'I', 'E'} '%')
WHERE NOT EXISTS (
    SELECT DISTINCT UPPER("Name") FROM table_2
    WHERE MAX(table_2."Date Collected")
    AND table_2."Income" = 'y'
    AND UPPER(table_2."Name") LIKE CONCAT('%', UPPER(table_1."Name"), '%')
)

aggregate functions are not allowed in "WHERE"

So I changed it to:

SELECT count(DISTINCT "Name")
FROM table_1
WHERE table_1.Height NOT LIKE ANY CONCAT('%', {'I', 'E'} '%')
WHERE NOT EXISTS (
    SELECT DISTINCT UPPER("Name") FROM table_2
    WHERE  table_2."Income" = 'y'
    AND UPPER(table_2."Name") LIKE CONCAT('%', UPPER(table_1."Name"), '%')
    AND HAVING MAX(table_2."Date Collected")
)

This gives me a syntax error. Removing the AND before HAVING gets me:

argument of HAVING must be type boolean, not type date

which I assume that there should be some condition after using . If I have to use HAVING, what should the condition be if I want to get the max date? Can I get the max date without using HAVING?

Answer 1

You can get the maximum date collected from table 2, and then use a NOT EXISTS clause to filter out the names that appear in table 2.

Here's the modified query -

SELECT DISTINCT "Name"
FROM table_1
WHERE table_1.Height NOT LIKE ANY (CONCAT('%', ARRAY['I', 'E']) || '%')
AND NOT EXISTS (
SELECT 1
FROM table_2
WHERE table_2."Name" = table_1."Name"
AND table_2."Date_Collected" = (
    SELECT MAX("Date_Collected")
    FROM table_2
    WHERE "Name" = table_1."Name"
  )
)