MAX DATE QUERY 3

[netrusher]

Below is SQL for a Query I have.

It is giving me the max date but I really want only the Max date for Operation_1.
If there are multiple entrys for Operation_ then I am getting multiple Max dates. Does
anyone know how I can modify the query to show Max date for Operation_1 only
once?

SELECT OPREF.PROCESS, OPREF.OPERATION, OPREF.OPERATION_, OPREF.OPERATIO_1, Max(OPREF.EFFECTIVE_) AS MaxOfEFFECTIVE_
FROM OPREF
GROUP BY OPREF.PROCESS, OPREF.OPERATION, OPREF.OPERATION_, OPREF.OPERATIO_1
ORDER BY OPREF.PROCESS, OPREF.OPERATION, Max(OPREF.EFFECTIVE_);

 

[AlexCuse]

You need to join the table to itself.
How about this (assumes Operatio_1 is the column you want to group by to get max effective date):

SELECT a.PROCESS, a.OPERATION, a.OPERATION_, a.OPERATIO_1, a.EFFECTIVE_ AS MaxOfEFFECTIVE
FROM OPREF a
INNER JOIN
(
SELECT OPERATIO_1
, Max(EFFECTIVE_) as MaxEff
FROM OPREF
GROUP BY OPERATION_1
) b
on a.OPERATION_1 = b.OPERATIO_1
and a.EFFECTIVE_ = b.MaxEff

Hope it helps,

ALex

Ignorance of certain subjects is a great part of wisdom

 

[Remou]

If you include several fields, you are likely to get several dates. This:

OPREF.OPERATION, OPREF.OPERATION_, OPREF.OPERATIO_1,

Suggests your table is not normalised.

You say you want max date for OPREF.OPERATIO_1, but the SQL shows:

Max(OPREF.EFFECTIVE_)

 

[netrusher]

Remou,

What I mean is if there are two records with Operatio_1 I only want the one if the latest date. This table is a download so it will never be normalized. Sometimes there might be multiple records with the same Process & Operatio_1. If that is the case I only want the one with the latest date.

 

[netrusher]

Alex,

I get the following error with the SQL you posted:

You tried to execute a query that does not include the specified expression "OPERATIO_1' as part of an aggregate function.

 

[Remou]

How about:
[tt]SELECT OPREF.PROCESS, OPREF.OPERATION, OPREF.OPERATION_, OPREF.OPERATIO_1, Max(OPREF.EFFECTIVE_) AS MaxOfEFFECTIVE_
FROM OPREF
WHERE OPREF.EFFECTIVE_=(Select Max(OPREF.EFFECTIVE_) From OPREF)[/tt]

 

[netrusher]

Thanks Remou,

I am getting the following error with that code:

You tried to execute a query that does not include the specified expression 'PROCESS' as part of an aggregate function.

 

[PHV]

Perhaps this ?
SELECT A.PROCESS, A.OPERATION, A.OPERATION_, A.OPERATIO_1, M.MaxOfEFFECTIVE_
FROM OPREF AS A INNER JOIN (
SELECT OPERATIO_1, Max(EFFECTIVE_) AS MaxOfEFFECTIVE_ FROM OPREF GROUP BY OPERATIO_1
) AS M ON A.OPERATIO_1 = M.OPERATIO_1 AND A.EFFECTIVE_ = M.MaxOfEFFECTIVE_
ORDER BY A.PROCESS, A.OPERATION, M.MaxOfEFFECTIVE_

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

Oops:

SELECT OPREF.PROCESS, OPREF.OPERATION, OPREF.OPERATION_, OPREF.OPERATIO_1, OPREF.EFFECTIVE_
FROM OPREF
WHERE OPREF.EFFECTIVE_=(Select Max(OPREF.EFFECTIVE_) From OPREF)

 

[AlexCuse]

PH's is what I was after. I accidentally typed OPERATION_1 rather than OPERATIO_1 in the group by portion of the subquery.

Ignorance of certain subjects is a great part of wisdom

 

[netrusher]

PH & Remou,

PH the SQL you sent is kind of working. Some of the Processes have only one Operatio_ and they are all 10. When I run the Query it is leaving off a lot of Processes. There are 22 Processes and I am getting info on 5. Each Process has Operatio_. Some only one (10), some more than one (10) (20) (30) etc. I need to see the Operatio_ for each Process with the latest date. I am getting the Latest date for the Process and Operatio_ the query is retrieving. It is just not showing each Process & Operatio_. Any more suggestions. I thank you both for your help and I hope I have explained this correctly.

 

[AlexCuse]

Maybe this (I think I fix the typo also):

SELECT a.PROCESS, a.OPERATION, a.OPERATION_, a.OPERATIO_1, a.EFFECTIVE_ AS MaxOfEFFECTIVE
FROM OPREF a
INNER JOIN
(
SELECT OPERATIO_1
[b], PROCESS[/b]
, Max(EFFECTIVE_) as MaxEff
FROM OPREF
GROUP BY OPERATIO_1[b], PROCESS[/b]
) b
on a.OPERATION_1 = b.OPERATIO_1
[b]and a.PROCESS = b.PROCESS[/b]
and a.EFFECTIVE_ = b.MaxEff

You didn't mention you wanted it grouped by process as well in your inital post ;-)

HOpe this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[netrusher]

Alex look great! Thanks!

Now if I only understood what you was doing!!

 

[AlexCuse]

I will try a quick explanation. This piece of the query:

(
SELECT OPERATIO_1
, PROCESS
, Max(EFFECTIVE_) as MaxEff
FROM OPREF
GROUP BY OPERATIO_1, PROCESS
) b

Is called a subquery (or derived table, depending on who you ask). Basically, what we are doing is treating the data returned by this query as a second table, with the alias b. If you run this query by itself, it will return OPPERATIO_1, PROCESS, and max(EFFECTIVE_) (for each OP/PROCESS Pairing).

Then, this piece of the query:

on a.OPERATION_1 = b.OPERATIO_1
and a.PROCESS = b.PROCESS
and a.EFFECTIVE_ = b.MaxEff

tells the INNER JOIN what columns to join on. Notice we have selected all three, to get the tightest match. The only way there will be a duplicate is if you have >1 row in your table with the exact same values in all three of these columns.

As you found, it does not work to take the max date, when you need to group by all columns you are selecting. So you need to get the max date based on the three columns you know will be the same between all rows you have in mind, then retrieve the matching records (including ALL columns) from your table based on this information.

Does that make sense?

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[netrusher]

That helps a lot! Thanks again.

 

[jodjim]

Hi AlexUse,

I'm in similar situation as netrusher and your solution is leading me to the right direction. My query is suppose to get the latest payrate of an employee from a rate table. Unfortunately the query is giving out more than one result (rate). The second part of the query look like this:

Select EMPLID,COMPRATE,MAX(EFFDT) From qSQL_Job_Pay GROUP BY EMPLID,COMPRATE

The result is:
EmpID Rate EffDate
1 10.00 4/01/06
1 12.00 4/01/07

Hope you can help me sort it out. Thanks.

 

[jodjim]

Sorry, the entire query is:

SELECT A.Employee_Name, A.Employee_ID, A.PayDate, A.Hours, B.COMPRATE FROM qSQL_Pay_Records A inner JOIN (Select EMPLID,COMPRATE,MAX(EFFDT) From qSQL_Job_Pay B GROUP BY EMPLID,COMPRATE) B on A.Employee_ID=B.EMPLID

 

[PHV]

What about this ?
SELECT A.Employee_Name, A.Employee_ID, A.PayDate, A.Hours, C.COMPRATE
FROM (qSQL_Pay_Records A INNER JOIN (
SELECT EMPLID,Max(EFFDT) AS LastDT FROM qSQL_Job_Pay GROUP BY EMPLID
) B ON A.Employee_ID=B.EMPLID)
INNER JOIN qSQL_Job_Pay C ON B.EMPLID=C.EMPLID AND C.LastDT=C.EFFDT

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jodjim]

Thanks, PHV. It's working.