Select the Employee names who held max positions

[brl51274]

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..!!

[jamesravid]

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

[brl51274]

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

James, Can U be more clear Pl..!! And I want the Count of Positions held and not the name of the position