开发者

Returning max value of multiple related rows and incorporate data from another table (SQL Server 2005)

开发者 https://www.devze.com 2023-02-16 14:51 出处:网络
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

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
0

精彩评论

暂无评论...
验证码 换一张
取 消