How can I select the max for a maths operation and also other elements without the max displaying all results

Question

I am trying to find the maximum value of something, while also selecting other attributes at the same time. When i do the max statement on its own, it works as intended, but when I select another attribute it displays every attribute as if the max statement wasn't there. How do I get this to work?

code that works:

select max(contract_end - contract_start)
from contract 

code that returns all data, not just the max:

select contract_id, emp_name, job_desc, max(contract_end - contract_start)
from contract join employer on emp_id = contract_client
              join job on job_id = contract_job
group by contract_id, emp_name, job_desc

Answer 1

The way I read it, would your query - slightly modified, so that it uses the MAX function in its analytic form - do the job?

select contract_id, 
       emp_name, 
       job_desc, 
       max(contract_end - contract_start) over (order by null) max_end_start
from contract join employer on emp_id = contract_client
              join job on job_id = contract_job

---

Or, is this what you're looking for? Use the MAX date difference as a subquery:

select contract_id, 
       emp_name, 
       job_desc,
       contract_end - contract_start diff
from contract join employer on emp_id = contract_client
              join job on job_id = contract_job
where contract_end - contract_start = (select max(contract_end - contract_start)
                                       from contract
                                      )