SQL query to find the highest paid salary for each lanauges

Question

As I am not expert in writing the SQL queries so want for help. I have a table called programmer whose structure & data look 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 a query to display the names of highest paid programmer for each language, which means I need to display the maximum salary & person name for each language. I tried my best to get the result but didn't get the answer. Can you help me?

Answer 1

While I like Gordon's answer, you can do it with a common table expression and a simple left join;

WITH cte AS (
  SELECT PNAME, SALARY, PROF1 PROF FROM programmer
  UNION 
  SELECT PNAME, SALARY, PROF2      FROM programmer
)
SELECT p1.PNAME, p1.PROF, p1.SALARY
FROM cte p1
LEFT JOIN cte p2
  ON p1.PROF = p2.PROF AND p1.SALARY < p2.SALARY
WHERE p2.PNAME IS NULL;

EDIT: An SQLfiddle for testing.

The union flattens PROF1 and PROF2 to separate rows, and the left join basically finds programmers where there exists no better paid programmer with the same proficiency.