• 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