Select the Employee names who held max positions
Write a query for this..!! i want to select the employee names who held max positions in the company. I want to display empname, positioncount. I have 2 tables where position, empno are in one table (jobhistory) and empno, empnames are in another table(employee). Select surname from employee where employee.empno in( select empno from jobhistory group by empno having count(*)=(select max(count(position)) from jobhistory group by empno)) the above query returns the empnames who held max positions in the company but i want to display the position count aswell..!!
Re: Select the Employee names who held max positions
Try this query,
Select a.surname,b.positions
from employee,( select empno,count(*) positions
from jobhistory
group by empno having count(*)=(select max(count(position)) from jobhistory group by empno)) b
where a.empno=b.empno
Re: Select the Employee names who held max positions
[QUOTE=jamesravid;10267]Try this query,
Select a.surname,b.positions
from employee,( select empno,count(*) positions
from jobhistory
group by empno having count(*)=(select max(count(position)) from jobhistory group by empno)) b
where a.empno=b.empno[/QUOTE]
James, Can U be more clear Pl..!! And I want the Count of Positions held and not the name of the position