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).
我需要从两个表中返回数据 - 一个表包含一对一级别的记录(TableB),另一个表包含一对多级别的记录(TableA)。从一对多表中我只想为每个唯一的CustomerAccountNumber返回一行,并且我希望基于一对一表中哪条记录具有最高DollarAmount值的条件。因此,即使表之间的链接是RequestNumber,我主要关注CustomerAccountNumber列(结果中甚至不需要RequestNumber)。
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 :)
只是很难将我的脑袋缠绕在获得理想结果所需的逻辑上。我在这里搜索了一些其他的max()函数问题,但找不到任何我正在寻找的东西。这是我在这里的第一篇文章,所以我愿意接受有关我的问题前搜索和/或问题结构的任何建设性反馈:)
Sample Data
TableA: one-to-many
RequestNumber CustomerAccountNumber 12345 987456 54321 987456 32145 987456
TableB: one-to-one
RequestNumber DollarAmount 12345 13000 54321 15000 32145 17000
Desired output after query (w/ 32145 having the highest DollarAmount value
查询后所需的输出(w / 32145具有最高的DollarAmount值
CustomerAccountNumber DollarAmount 987456 17000
1 个解决方案
#1
2
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
#1
2
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