MAX record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

MAX record

Author

Topic

Mango87
Starting Member

1 Post

Posted - 2014-05-28 : 06:11:34

Hi,

I have the following select statement and I want to export the details (ValueDate and FundValue) for the maximum id_ValuationRequest per [PLAN].PlanNumber. Can anyone help? Sorry, I am quite new to SQL and I am trying to teach myself...

SELECT dbo.[PLAN].PlanNumber, dbo.VALUATIONREQUEST.ValueDate, dbo.VALUATIONREQUEST.FundValue,
dbo.VALUATIONREQUEST.id_ValuationRequest
FROM dbo.VALUATIONREQUEST INNER JOIN
dbo.VALUETYPE ON dbo.VALUATIONREQUEST.id_ValueType = dbo.VALUETYPE.id_ValueType INNER JOIN
dbo.[PLAN] ON dbo.VALUATIONREQUEST.UniquePlanID = dbo.[PLAN].UniquePlanID INNER JOIN
dbo.VALUESTATUS ON dbo.VALUATIONREQUEST.id_ValueStatus = dbo.VALUESTATUS.id_ValueStatus
WHERE (dbo.VALUETYPE.ValueTypeName LIKE 'Contractual%') AND (dbo.VALUESTATUS.ValueStatusName = 'Complete and Checked' OR
dbo.VALUESTATUS.ValueStatusName = 'Checked') AND (dbo.VALUATIONREQUEST.FundValue <> 0)
ORDER BY dbo.[PLAN].PlanNumber

EXRORT DETAILS....

PlanNumber ValueDate FundValue id_ValuationRequest
A 31/03/2010 100 113
A 30/04/2010 200 114
A 30/04/2010 100 185
B 01/10/2010 1000 125
B 01/01/2014 2000 178
B 06/05/2014 300 268
C 01/01/2013 6000 758
C 05/04/2011 7000 687
C 20/02/2009 800 249

I would expect to see three rows of results for id's 185(plan A), 268 (plan B) & 758 (plan C).

Also, how would I change the date format to YYYY-MM-DD?

Thanks so much in advance. So happy I found this website!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-28 : 07:51:03

basically something like:


select max(id_ValuationRequest)
from ...
group by [PLAN].PlanNumber
order by [PLAN].PlanNumber

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-05-28 : 10:22:33

please try this:

SELECT dbo.[PLAN].PlanNumber, dbo.VALUATIONREQUEST.ValueDate, dbo.VALUATIONREQUEST.FundValue, 
 dbo.VALUATIONREQUEST.id_ValuationRequest
FROM dbo.VALUATIONREQUEST INNER JOIN
 dbo.VALUETYPE ON dbo.VALUATIONREQUEST.id_ValueType = dbo.VALUETYPE.id_ValueType INNER JOIN
 dbo.[PLAN] ON dbo.VALUATIONREQUEST.UniquePlanID = dbo.[PLAN].UniquePlanID INNER JOIN
 dbo.VALUESTATUS ON dbo.VALUATIONREQUEST.id_ValueStatus = dbo.VALUESTATUS.id_ValueStatus
 
inner join
 (
SELECT dbo.[PLAN].PlanNumber, max(dbo.VALUATIONREQUEST.id_ValuationRequest) as id_ValuationRequest
FROM dbo.VALUATIONREQUEST INNER JOIN
 dbo.VALUETYPE ON dbo.VALUATIONREQUEST.id_ValueType = dbo.VALUETYPE.id_ValueType INNER JOIN
 dbo.[PLAN] ON dbo.VALUATIONREQUEST.UniquePlanID = dbo.[PLAN].UniquePlanID INNER JOIN
 dbo.VALUESTATUS ON dbo.VALUATIONREQUEST.id_ValueStatus = dbo.VALUESTATUS.id_ValueStatus
WHERE (dbo.VALUETYPE.ValueTypeName LIKE 'Contractual%') AND (dbo.VALUESTATUS.ValueStatusName = 'Complete and Checked' OR
 dbo.VALUESTATUS.ValueStatusName = 'Checked') AND (dbo.VALUATIONREQUEST.FundValue <> 0)
 group by dbo.[PLAN].PlanNumber
) as dt
on dt.PlanNumber = dbo.[PLAN].PlanNumber and dt.id_ValuationRequest = dbo.VALUATIONREQUEST.id_ValuationRequest
 
WHERE (dbo.VALUETYPE.ValueTypeName LIKE 'Contractual%') AND (dbo.VALUESTATUS.ValueStatusName = 'Complete and Checked' OR
 dbo.VALUESTATUS.ValueStatusName = 'Checked') AND (dbo.VALUATIONREQUEST.FundValue <> 0)

ORDER BY dbo.[PLAN].PlanNumber

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources