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

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

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

This is clearly school work. Post your best attempt and we will give
you pointers ... not solutions.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan wrote:

> This is clearly school work. Post your best attempt and we will give
> you pointers ... not solutions.

That may well be true, but I feel compelled to point out that I had a
very similar requirement in the real world. (Although, I didn't solve
it with a view. As part of a barcoding/job cost tracking system, the
program mails appropriate managers when people obviously forget to
clock out of a job - or, as happened recently, the UPS screws up and
they can't clock out and no one has access to voice mail or email
and...)

So do your schoolwork folks, don't expect usenet to bail you out! It
pays off in the real world.

jg
--
@home.com is bogus. "The sad truth is the job pays too well - I am an
Oracle whore and Larry is my pimp. In order for me to be truely happy
in my job I must master my fears and really get to grips with Oracle -
it's architecture and the strange gubbins inside the mysterious
machine." - Alan Barton

joel garry wrote:
<snip>
> So do your schoolwork folks, don't expect usenet to bail you out! It
> pays off in the real world.

Isn't it great that school is back in session?