Select record with highest value for field hours from multiple rows

Hello,

I've got a table with the following columns: EmployeeNumber, Company, Department, Cluster, Job, Hours

There can be multiple records for one EmployeeNumber. An employee can work for multiple companies, for multiple departments within a company, for multiple clusters within a department or can have multiple jobs within a cluster.

So, there can be multiple jobs for a specific combination of EmployeeNumber, Company, Department and Cluster. I want to create a view that selects only one record per EmployeeNumber, Company, Department, Cluster combination. It has to select the job that has the highest value for the field Hours.

How can I do that?

Kind regards,

Willy Tadema Received on Fri Sep 01 2006 - 07:34:42 CDT