create table #temp
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10),
col5 int
)
insert into #temp
select 'A','AA','AAA','KA',10
union all
select 'A','AA','AAA','KB',20
union all
select 'A','AA','AAA','KC',30
union all
select 'B','BB','BBB','LB',40
union all
select 'B','BB','BBB','LC',50
union all
select 'B','BB','BBB','LD',60
select a.col1,a.col2,a.col3,a.col4,a.col5
from #temp a,
(
select a.col1,a.col2,a.col3,max(a.col5)col5
from #temp a
group by a.col1,a.col2,a.col3
) b
where b.col1 =a.col1
and b.col2 = a.col2
and b.col3 = a.col3
and b.col5 = a.col5
order by a.col1,a.col2,a.col3