英文:
Selecting another field in grouped rows by the maximum value of another field
问题
我需要一点关于SQL的帮助。我想要获取每个KeyCon组中CallsCount值最大的记录中的Address字段的值。
要输出的结果如下：
| KeyCon | Address |
|---|---|
| 100 | Build9 |
| 101 | Build7 |
| 102 | Build3 |
英文:
I need a little help with SQL. I want to get the value of the field Address from the table where CallsCount is maximum for each KeyCon group.
| Key | KeyCon | Adress | CallsCount |
|---|---|---|---|
| 1 | 100 | Build9 | 15 |
| 2 | 100 | Build0 | 10 |
| 3 | 101 | Build7 | 24 |
| 4 | 101 | Build4 | 17 |
| 5 | 102 | Build3 | 10 |
At the output I want to get
| KeyCon | Adress |
|---|---|
| 100 | Build9 |
| 101 | Build7 |
| 102 | Build3 |
答案1
得分: 0
以下是您提供的代码的翻译结果：
尝试这个
创建表测试（[键] int，KeyCon int，地址 varchar（30），CallsCount int）;
向测试中插入值（1，100，'Build9'，15）;
向测试中插入值（2，100，'Build0'，10）;
向测试中插入值（3，101，'Build7'，24）;
向测试中插入值（4，101，'Build4'，17）;
向测试中插入值（5，102，'Build3'，10）;
向测试中插入值（6，102，'Build3-2'，10）;
从
（选择KeyCon，最大（callscount）maxqty
从测试
按KeyCon分组
）g
内连接测试t on t.keycon=g.keycon and callscount=maxqty
按g.keycon排序
我将只返回翻译好的部分，不包括任何其他内容。
英文:
Try this
create table test ([Key] int, KeyCon int, Adress varchar(30), CallsCount int);
insert into test values(1, 100,'Build9', 15);
insert into test values(2, 100,'Build0', 10);
insert into test values(3, 101,'Build7', 24);
insert into test values(4, 101,'Build4', 17);
insert into test values(5, 102,'Build3', 10);
insert into test values(6, 102,'Build3-2', 10);
select * from
(select KeyCon,max(callscount) maxqty
from test
group by KeyCon
) g
inner join test t on t.keycon=g.keycon and callscount=maxqty
order by g.keycon
I'll add 1 row to your test data, where 2 rows have equal CallsCount. Both lines will be included in the result.
| KeyCon | maxqty | Key | KeyCon | Adress | CallsCount |
|---|---|---|---|---|---|
| 100 | 15 | 1 | 100 | Build9 | 15 |
| 101 | 24 | 3 | 101 | Build7 | 24 |
| 102 | 10 | 5 | 102 | Build3 | 10 |
| 102 | 10 | 6 | 102 | Build3-2 | 10 |
答案2
得分: 0
如果您的数据库管理系统支持的话，您可以使用 row_number 函数。
select keyCon, address
from (
select keyCon, address,
row_number() over (partition by keyCon order by callsCount desc) as rn
from my_table
)z
where rn = 1;
| keyCon | address |
|---|---|
| 100 | Build9 |
| 101 | Build7 |
| 102 | Build3 |
英文:
If your DBMS supports it, you could use the row_number function.
select keyCon, address
from (
select keyCon, address,
row_number() over (partition by keyCon order by callsCount desc) as rn
from my_table
)z
where rn = 1;
| keyCon | address |
|---|---|
| 100 | Build9 |
| 101 | Build7 |
| 102 | Build3 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论