see this example
select id,marks,type_ from
(
select dense_rank() over(partition by type_ order by type_,marks desc) as rank,* from
(
select 1 as id,100 as marks, 'm' as type_
union all
select 2 as id,99 as marks, 'm' as type_
union all
select 3 as id,100 as marks, 'm' as type_
union all
select 4 as id,100 as marks, 'E' as type_
union all
select 5 as id,99 as marks, 'E' as type_
) as a
) as a where rank=1
Happy coding!
:)