sql server - SQL query to find the highest paid salary for each lanauges -

as not expert in writing sql queries want help. have table called programmer structure & data like:

pname,prof1,prof2,salary 

in prof1 data are:

pascal,clipper,cobol,cpp,cobol,pascal,assembly,pascal,basic,c,pascal,foxpro.

in prof2 data are:

basic,cobol,dbase,dbase,oracle,dbase,clipper,c,dbase,cobol,assembly,basic,c.

in salary data are:

3200,2800,3000,2900,4500,2500,2800,3000,3200,2500,3600,3700,3500.

i need query display names of highest paid programmer each language, means need display maximum salary & person name each language. tried best result didn't answer. can me?

while gordon's answer, can common table expression , simple left join;

with cte (   select pname, salary, prof1 prof programmer   union    select pname, salary, prof2      programmer ) select p1.pname, p1.prof, p1.salary cte p1 left join cte p2   on p1.prof = p2.prof , p1.salary < p2.salary p2.pname null; 

edit: sqlfiddle testing.

the union flattens prof1 , prof2 separate rows, , left join finds programmers there exists no better paid programmer same proficiency.