CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Alan
Alan

Reputation: 51

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

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

Upvotes: 0

Views: 2604

Answers (3)

G.Arima
G.Arima

Reputation: 1171

Try this out:

proc sql;
select a.loan_id,p1.val,p1.p_count,p2.type,p2.address
from
loans l
left join
(select loan_id,max(appraised_value) as val,count(propoerty_id) as p_count
from propoerties
group by loan_id) p1
on l.id = p1.loan_id
left join
propoerties p2
on p1.loan_id = p2.loan_id and p1.val=p2.appraised_value;

Let me know in case you have any different requirement.

Upvotes: 0

user330315
user330315

Reputation:

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().

Upvotes: 4

Josh Gilfillan
Josh Gilfillan

Reputation: 5146

You can use the ROW_NUMBER() analytic function to rank the records:

select
   x.loan_id
  ,x.val
  ,x.p_count
  ,x.type
  ,x.address
from (
  select
     l.loan_id
    ,p.appraised_value as val
    ,p.type
    ,p.address
    ,row_number() over (partition by l.loan_id order by p.appraised_value desc) as ranker
    ,count(1) over (partition by l.loan_id) as p_count
  from loans l

  left join properties p
    on l.loan_id = p.loan_id
) x
where x.ranker = 1

Upvotes: 0

Related Questions