sql server - condition based select SQL query -

for example have table "info" below.

+--------+------------+------+------------+ | entity | department | code | code_count | +--------+------------+------+------------+ | e1     | d1         |  123 |          5 | | e1     | d1         |  234 |         10 | | e1     | d1         |  345 |         20 | | e1     | d2         |  456 |          2 | | e1     | d2         |  567 |          5 | | e1     | d2         |  678 |         10 | +--------+------------+------+------------+  

my query should function this: each entity , department pair, select code has maximum code count.

any appreciated. thanks.

select t1.* your_table t1 join (   select entity, department, max(code_count) code_count    your_table   group entity, department ) t2 on t1.entity = t2.entity      , t1.department = t2.department      , t1.code_count = t2.code_count