Return latest value

Hi Experts, I have a table - columns A,B,C are the primary key, column D is a date, and E is a varchar.

What I need is for eack PK, I want the data for column E returned for the latest date - leaving behing all earlier records for each PK. Try as I might, I just can't see it - been coding too long today.

Kelvin
LVL 23
Kelvin SparksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alpha AuCommented:
try this?

select A,B,C,D,E from table1

inner join 
(
select A,B,C, Max(D) as MAXD from table1
group by A,B,C
) maxvalue

where table1.A = maxvalue.A
and table1.B = maxvalue.B
and table1.C = maxvalue.C
and table1.D = maxvalue.MAXD

Open in new window

0
Kelvin SparksAuthor Commented:
Code below - syntax error at WHERE that occurs after the MaxValue - error message is: Incorrect syntax near the keyword 'WHERE'
(SELECT wr.Priority FROM AQD.qaoc_Work_Request wr INNER JOIN (SELECT WR_Period, WR_Type, WR_Number, MAX(Target_Start_Date) as Maxp FROM AQD.qaoc_Work_Request GROUP BY WR_Period, WR_Type, WR_Number) MaxValue
						WHERE wr.WR_Period = MaxValue.WR_Period AND wr.WR_Type = MaxValue.WR_Type AND wr.WR_Number = MaxValue.WR_Number and wr.Target_Start_Date =  MaxValue.Maxp)

Open in new window

0
Alpha AuCommented:
Oh... my fault, it should be ON instead of Where.
SELECT wr.Priority 
FROM AQD.qaoc_Work_Request wr 
INNER JOIN 
(
SELECT 
WR_Period, WR_Type, WR_Number, MAX(Target_Start_Date) as Maxp 
FROM AQD.qaoc_Work_Request 
GROUP BY WR_Period, WR_Type, WR_Number
) MaxValue

ON wr.WR_Period = MaxValue.WR_Period 
AND wr.WR_Type = MaxValue.WR_Type 
AND wr.WR_Number = MaxValue.WR_Number 
and wr.Target_Start_Date =  MaxValue.Maxp

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kelvin SparksAuthor Commented:
Awesome - thanks for the prompt help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.