Selecting another field in grouped rows by the maximum value of another field

Question

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

[SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) ? — Ponder Stibbons, Apr 13 '23 at 13:23

score 0 · Answer 1 · answered Apr 13 '23 at 13:29

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

score 0 · Accepted Answer · answered Apr 13 '23 at 14:08

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

Selecting another field in grouped rows by the maximum value of another field

2 Answers2