SQL query help...

[Jumpem]

I have a table with the following data in the rows:

scheduleNum...|...jobTitle
41789..................031 FT Engineer Management Trainee
41790..................031 FT Field Construction Engineer

45897..................031 FT Engineer Management Trainee
45898..................031 FT Field Construction Engineer

The rows are duplicates except for the scheduleNum. I need to be able to check if the jobTitle's are the same and then return the rows with the higher scheduleNum. Can anyone give me any ideas please?

Thanks, Justin

 

[Entity]

In MySQL:

SELECT max(scheduleNum), jobTitle FROM jobs GROUP BY jobtitle;

Rob

 

[Haircut]

What Entity said.
That will give you the highest schedule num for each job titlle.

It's ANSI standard SQl, so should work in any db,

One thing I will point out thiough is that you should have anohter tabel for teh job titles.

Engineer Management Trainee shouldn;t appear twice, you should have an0ther tabel wuth the job titles and use the priamry key in this table as the link in the tabke you have shown.

PS. Please forgive any typos, as I am pretty drunk!

 

[Descartes]

Originally posted by: Haircut
What Entity said.
That will give you the highest schedule num for each job titlle.

It's ANSI standard SQl, so should work in any db,

One thing I will point out thiough is that you should have anohter tabel for teh job titles.

Engineer Management Trainee shouldn;t appear twice, you should have an0ther tabel wuth the job titles and use the priamry key in this table as the link in the tabke you have shown.

PS. Please forgive any typos, as I am pretty drunk!

You're lucid enough to use your computer, browse to this forum, AND respond to a technical question; yet, you somehow can't type properly?

 

[Jumpem]

Originally posted by: Haircut
What Entity said.
That will give you the highest schedule num for each job titlle.

It's ANSI standard SQl, so should work in any db,

One thing I will point out thiough is that you should have anohter tabel for teh job titles.

Engineer Management Trainee shouldn;t appear twice, you should have an0ther tabel wuth the job titles and use the priamry key in this table as the link in the tabke you have shown.

PS. Please forgive any typos, as I am pretty drunk!

I have no choice about how the original tables are laid out. We download and update data on a nightly basis from another company. I split the title and schedule number into a temp table, picked the highest, and then joined it to the rest of the original row. It works now.

 

[oog]

One thing that many people do when they import data from another system is to put it into staging tables, and from there move it to your production tables. During the second step, they transform the data so that it fits their own data model.

This is something you might want to consider as a part of making your data more normalized.

 

[Jumpem]

Originally posted by: oog
One thing that many people do when they import data from another system is to put it into staging tables, and from there move it to your production tables. During the second step, they transform the data so that it fits their own data model.

This is something you might want to consider as a part of making your data more normalized.

Right, I have several DTS's importing and transforming data. My system has three main databases with about a hundred tables in each one. I didn't want to transform that portion of the data as it is used throughout several asp pages in that format. What a learning experience this job is.