Retrieve the highest value in a one-to-many-relationship

Question

I have a loan table and a properties table. One to many relationship. If the loan contains more than one property, I have to only retrieve the property that has the highest appraisal value.

The following query

SELECT l.loan_id,p_count
FROM loans lo
JOIN
  (SELECT loan_id, MAX(appraised_value) AS val
          COUNT(property_id) AS p_count
   FROM properties
   GROUP BY loan_id) AS pc ON pc.loan_id = lo.id

gives me the output

loan_id val      p_count
817     914,000  2

But if I attempt to retrieve additional attributes (e.g type, address) from the properties table, I get records all the properties for that loan (2, in this case). I wrote this query

SELECT l.loan_id,p_count
FROM loans lo
JOIN
  (SELECT loan_id, MAX(appraised_value), type, address AS val
          COUNT(property_id) AS p_count
   FROM properties
   GROUP BY loan_id) AS pc ON pc.loan_id = lo.id

to get the following output:

loan_id val      p_count  type     address
817     800,000  2        duplex   123 main street
817     914,000  2        triplex  234 cedar avenue

How can I get the following output for the highest appraised value?

loan_id  val      p_count  type      address
817      914,000  2        triplex   234 cedar avenue

Answer 1

You can use window functions to calculate the count of properties per loan and the highest value in the same query:

SELECT lo.id, pc.p_count, pc.appraised_value, pc.type, pc.address
FROM loans lo
JOIN (
  SELECT loan_id, 
         appraised_value,
         type,
         address,
         count(*) over (partition by loan_id) AS p_count,
         dense_rank() over (partition by loan_id order by appraised_value desc) as rnk
   FROM properties
) AS pc ON pc.loan_id = lo.id and rnk = 1;

As Josh commented, if two properties have the same highest appraised_value, both will be listed. If you don't want that, use row_number() instead of dense_rank().