Oracle select with aggregate but not group by all columns

Question

I have a query I am trying to run with the following requirements:

Join two tables: Employee and EmployeeDepartment

Employee: Employee_ID, FirstName, LastName

EmployeeDepartment: Employee_ID, DepartmentName, StartDate

I am trying to run a query that will join the two tables and display a count of the number of departments each employee has been in and the start date for the most recent department. Is there a way to do this without doing a subselect? It seems I want to use an Oracle aggregate (max) but not group by StartDate?

Thank you!

Answer 1

SELECT e.firstname, e.lastname, max(ed.startdate), count(ed.departmentName)
  FROM employee e, employeeDepartment ed
 WHERE e.employee_id = ed.employee_id
 GROUP by e.firstname, e.lastname