Select first record with join from 1:1 to 1:n relation - postgres

Question

I'm trying to join two tables in postgresql with the following structure, which is the relation between some areas and some points surrounding it

Table 1 "d_hid_t"

id_rank | dem_hid_m3s
   1          3.6
   2          3.45
   3          3.40
   4          2.35
   5          2.33

Table 2 "disph"

id_rank |   disph_m3s
   1          0.058
   1          1.36
   1          1833.4
   2          0.017
   2          208.69
   3          0.39
   3          40.79
  ...          ...

I'm expecting to get the join output based on the maximum value found for the unique ID of table 1, without repeating the other related values, for example

Table 3 "output"

id_rank |   disph_m3s
   1          1833.4
   2          208.69
   3           40.69     
  ...          ...

So far I followed some instruction on this stack overflow post Select first record in a One-to-Many relation using left join but with no success. I keep getting the error "aggregate functions are not allowed in JOIN conditions". Right now my code looks like this:

SELECT a.id_rank, a.dem_hid_m3s, a.geom, b.id_disp, b.dist_km, b.disph_m3s
FROM d_hid_t as a
LEFT JOIN disph as b 
ON a.id_rank = b.id_rank
AND (SELECT max(b.disph_m3s) WHERE a.dem_hid_m3s <= b.disph_m3s)

score 0 · Answer 1 · answered Jul 20 '21 at 17:52

A pretty simply method uses distinct on. I might use it before joining:

SELECT h.*, d.*
FROM d_hid_t h LEFT JOIN
     (SELECT DISTINCT ON (d.id_rank) d.*
      FROM disph d
      ORDER BY d.id_rank, disph_m3s DESC
     ) d
     USING (id_rank);

Note the use of meaningful table aliases rather than arbitrary letters.

Select first record with join from 1:1 to 1:n relation - postgres

1 Answers1