I need to return data from two tables - one table contains records on a one-to-one level (TableB) and the other contains records on a one-to-many level (TableA). From the one-to-many table I only want to return one row for each unique CustomerAccountNumber, and I want to base the criteria on which record from the one-to-one table has the highest DollarAmount value. So even though the link between the tables is the RequestNumber, I am mainly concerned with the CustomerAccountNumber column (don't even need RequestNumber in the results).
Just having a hard time wrapping my head around the logic needed to get the desired result. I searched through some other max() function questions here but couldn't find anything that does quite what I'm looking for. This is my first post here, so I'm open to any constructive feedback about my pre-question searching and/or question structuring :)
Sample Data
TableA: one-to-many
RequestNumber CustomerAccountNumber 12345 987456 54321 987456 32145 987456
T开发者_运维技巧ableB: one-to-one
RequestNumber DollarAmount 12345 13000 54321 15000 32145 17000
Desired output after query (w/ 32145 having the highest DollarAmount value
CustomerAccountNumber DollarAmount 987456 17000
SELECT customerAccountNumber, dollarAmount
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customerAccountNumber ORDER BY dollarAmount DESC) rn
FROM tableA a
JOIN tableB b
ON b.requestNumber = a.requestNumber
) q
WHERE rn = 1
精彩评论