How do I make max() function only out put a single row

Question

Select MAX(end_contract-start_contract), job_description, employer_name
FROM contracts join jobs on contracts_job = jobs_id
              join employers on contracts_client = employer_id
WHERE contracts_end is not NULL
GROUP BY jobs_id,jobs_desc,employer_name

This query currently outputs:

MAX(end_contract-start_contract) jobs_desc        employer_name                      
-------------------------------- --------------- ------------------------------
                             153 Janitor         Microsoft              
                              80 Soldier         Microsoft              
                             119 UNDEFINED       USPS       
                             290 UNDEFINED       Microsoft              
                              89 Pilot           USNC                     
                             119 Cook            USNC                    
                             232 driver          USNC                     
                             340 Soldier         USMC 

I want the output to be:

MAX(end_contract-start_contract) jobs_desc        employer_name                      
-------------------------------- --------------- ------------------------------
                             340 Soldier         USMC 

That's why I used max() but I can't figure out why it's not just showing the max value. I am very new to SQL

Answer 1

This might be more intuitive:

Select CONTRACT_END-CONTRACT_START, JOB_DESC, EMP_NAME
FROM CONTRACT join JOB on CONTRACT_JOB = JOB_ID
          join EMPLOYER on CONTRACT_CLIENT = EMP_ID
WHERE CONTRACT_END is not NULL
AND CONTRACT_END-CONTRACT_START=(Select MAX(CONTRACT_END-CONTRACT_START) FROM CONTRACT)