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
Advertisement
Answer
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 )