Hi @MattHeidebrecht ,

You can use ROW_NUMBER window function to order rows and then filter on that rank.

Here is the query:

WITH loc_ranked AS (
    SELECT
        IL_IPI_ID,
        IL_ID,
        ROW_NUMBER() OVER (PARTITION BY IL_IPI_ID ORDER BY IL_ID DESC) AS rn
    FROM Location
)

SELECT
    ipi.IPI_ID,
    loc_ranked.IL_ID
FROM Policy ipi
LEFT JOIN loc_ranked
    ON ipi.IPI_ID = loc_ranked.IL_IPI_ID
    AND loc_ranked.rn = 1;

Here is the end to end example with sample data:

CREATE OR REPLACE TEMP VIEW Policy AS
SELECT * FROM VALUES
  (1),
  (2),
  (3)
AS t(IPI_ID);

CREATE OR REPLACE TEMP VIEW Location AS
SELECT * FROM VALUES
  (1, 10),
  (1, 5),
  (2, 100),
  (2, 90),
  (3, 300),
  (3, 250),
  (3, 100)
AS t(IL_IPI_ID, IL_ID);

WITH loc_ranked AS (
    SELECT
        IL_IPI_ID,
        IL_ID,
        ROW_NUMBER() OVER (PARTITION BY IL_IPI_ID ORDER BY IL_ID DESC) AS rn
    FROM Location
)
SELECT
    ipi.IPI_ID,
    loc_ranked.IL_ID
FROM Policy ipi
LEFT JOIN loc_ranked
    ON ipi.IPI_ID = loc_ranked.IL_IPI_ID
    AND loc_ranked.rn = 1;

The result: