Problem in join using oracle


(system) #1

hello
i’ve quation about join
i try to solve it many times but it is not work with me
if and one can of help thanks
my Q
i’ve two tables employees and jobs
in employees there is employee_id,first_name,last_name,job_id,salary
in jobs job_id,job_title
i want to displays employee_id,first_name,last_name,salary and job_title
for all employees having the highest salary in the job_title.
i mean for example if there are three employees with job title engineer i should display information about the one who having the highest salary as engineer
i tried this solution but it’s not work
select employees.employee_id,employees.first_name,employe es.last_name,employees.salary,jobs.job_title
from employees inner join jobs
on employees.job_id=jobs.job_id,
(select job_id,max(salary) as maxsal
from employees
group by job_id) as maxtab
where employees.job_id=maxtab.job_id and employees.salary=maxtab.maxsal
order by jobs.job_id;
it give me
error at line 6:
ORA-00933: SQL command not properly ended
the line 6 here: group by job_id) as maxtab there * under as so i don’t know