select a.Number, b.Email, b.Type from ( select distinct Number from Test ) as a cross apply ( select top 1 Email, Type from Test where Number = a.Number order by case when Type = 'P' then 1 when Type = 'S' then 2 when Type = 'I' then 3 else 10 end ) as b
select * from test2 inner join (select number, min(type) as type from test2 group by number) as v1 on test2.number = v1.number and test2.type = v1.type
Select Number,Email,Type from TableName where Number in (Select min(Number) from TableName group by Number)
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)