Re: Select record with highest value for field hours from multiple rows

willy wrote:
> 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

And you tried to solve this how?

You can easily get one for per EmployeeNumber, Comapny, Deparment, Cluseter by using a group by and the use of a group by clause would allow you to find the MAX(hours) per group. Unfortunately, I think you will get a "not a group by column" error if you try to select job along with this.

But since you know the EmployeeNumber, Company, Department, Cluster, and Hours for the row you want I would think a join back to the original table on these columns would find the associated job.

That would be one way to go about solving this problem. I will leave the actual SQL to you.

HTH -- Mark D Powell -- Received on Fri Sep 01 2006 - 08:16:18 CDT