Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Return latest value

Posted on 2011-10-17
4
Medium Priority
?
290 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Kelvin Sparks
  • 2
  • 2
4 Comments
 
LVL 7

Expert Comment

by:Alpha Au
ID: 36983475
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
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 36983521
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
 
LVL 7

Accepted Solution

by:
Alpha Au earned 2000 total points
ID: 36983536
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
 
LVL 22

Author Closing Comment

by:Kelvin Sparks
ID: 36983545
Awesome - thanks for the prompt help
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question