LATEST DATE OF DUPLICATE ENTRY'S

[netrusher]

I have a table with fields that are duplicated and the only difference is the effective date. What I want my query to do is to find the latest effective date only for the duplicated fields. Example

abc 2006-08-28
abc 2006-09-29
abc 2006-10-28
abc 2006-10-29

def 2006-03-04
def 2006-05-05
def 2006-08-08

I would want my query to show only the record abc with the date 2006-10-29 and
the record def with 2006-08-08

I am hoping here is a easy way to accomplish this.

 

[AlexCuse]

This is pseudo code because I don't have your table info, but this should help you out.

select field1, field2.... , max(effective_date)
from table
group by field1, field2....

Hope it helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[netrusher]

Below is the SQL that I have. But I am still getting more than one effective date.

What am I doing wrong?

SELECT OPRESROP.PROCESS, OPRESROP.OPERATION_, OPRESROP.OPERATION, OPRESROP.OPERATIO_1, Max(OPRESROP.EFFECTIVE_)
FROM OPRESROP
GROUP BY OPRESROP.PROCESS, OPRESROP.OPERATION_, OPRESROP.OPERATION, OPRESROP.OPERATIO_1;

 

[lespaul]

for each combination of Process, Operation_, Operation and Operatio_1 you will get the max date. Which of these fields corresponds to the ABC and DEF you posted originally? You'll need to do an inner join of that field and the date. For instance, if those referred to PROCESS, then the query would be:


SELECT A.PROCESS, A.OPERATION_, A.OPERATION, A.OPERATIO_1, B.MaxOPRESROP.EFFECTIVE_
FROM OPRESROP A
INNER JOIN (SELECT PROCESS, MAX(OPRESROP.EFFECTIVE_) As MaxOPRESROP.EFFECTIVE_ FROM OPRESROP GROUP BY PROCESS) B ON A.Process = B.Process;


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

SELECT A.PROCESS, A.OPERATION_, A.OPERATION, A.OPERATIO_1, A.EFFECTIVE_
FROM OPRESROP AS A INNER JOIN (
SELECT PROCESS, Max(EFFECTIVE_) As EFFECTIVE_FROM OPRESROP GROUP BY PROCESS
) AS B ON A.PROCESS = B.PROCESS AND A.EFFECTIVE_ = B.EFFECTIVE_

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