Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Maximum Date Value 1

Status
Not open for further replies.

Taff82

Programmer
Feb 11, 2004
43
GB
Hi all,

Not sure how to word this but here goes.

I have a query with three tables as follows:-

Code:
SELECT [Learner Dataset].LEARN_ID, [Learner Dataset].PROVI_ID, [Learner Dataset].LSURNAME, [Learner Dataset].LFORENAM, [Learner Dataset].NAT_INSU, [Learning Programme Dataset].TYPE_LPG, [Learning Programme Dataset].TRAINING_ORG, [Learning Activity Dataset].LACTI_ID, [Learning Activity Dataset].LAIM_REF, [Learning Activity Dataset].AWARD_BOD, [Learning Activity Dataset].TITLE_LA, [Learning Activity Dataset].STARTDTE, [Learning Activity Dataset].EENDDATE, [Learning Activity Dataset].TERMDATE, [Learning Activity Dataset].COMPSTAT
FROM ([Learner Dataset] INNER JOIN [Learning Programme Dataset] ON ([Learner Dataset].PROVI_ID = [Learning Programme Dataset].PROVI_ID) AND ([Learner Dataset].LEARN_ID = [Learning Programme Dataset].LEARN_ID)) INNER JOIN [Learning Activity Dataset] ON ([Learning Programme Dataset].LPROG_ID = [Learning Activity Dataset].LPROG_ID) AND ([Learning Programme Dataset].PROVI_ID = [Learning Activity Dataset].PROVI_ID) AND ([Learning Programme Dataset].LEARN_ID = [Learning Activity Dataset].LEARN_ID)
WITH OWNERACCESS OPTION;

What I would like to do is group the query firstly by Learn_ID, then by LPROG_ID and then show the Record within the group with the highest or maximum EENDDATE.

Not sure if i've explained myself correctly.

Thanks

Taff
 
You could try using grouping and select the Maximum option for your EENDDATE
 
Name the above query and substitute the query name for the red code below:

Code:
Select A.* 
from [i][red]yourqueryname[/red][/i] as A 
WHERE (A.[LEARN_ID] & A.[PROVI_ID] & A.[EENDDATE]) IN (Select (B.[LEARN_ID] & B.[PROVI_ID] & MAX(B.[EENDDATE])) AS SelectedRecord FROM [i][red]yourqueryname[/red][/i] as B 
GROUP BY B.[LEARN_ID], B.[PROVI-ID]);

Let me know if this works. This is pseudo code and I have not been able to test it. Will rely upon you for that.

[COLOR=006633]Bob Scriver[/color]
MIState1.gif
[COLOR=white 006633]MSU Spartan[/color]
 
Thanks Scriverb,

Have tried wht you said and I seem to be getting the error:-

You tried to execute a query that does not include the specified expression 'B.[LEARN_ID] & MAX(B.ENDDATE])' as part of an aggregate function.

Any ideas?

Regards,

Taff
 
Okay, give this a try:

Code:
GROUP BY (B.[LEARN_ID] & B.[PROVI-ID]);

This is a little tricky but so it may take a couple of attempts to get the group by correct.

[COLOR=006633]Bob Scriver[/color]
MIState1.gif
[COLOR=white 006633]MSU Spartan[/color]
 
Hi scriverb,

Nope still no luck.

Taff
 
Have you tried something like this ?
SELECT A.*
FROM yourqueryname A INNER JOIN
(SELECT LEARN_ID, PROVI_ID, Max(EENDDATE) As MaxDate
FROM yourqueryname GROUP BY LEARN_ID, PROVI_ID
) B ON (A.LEARN_ID=B.LEARN_ID) AND (A.PROVI_ID=B.PROVI_ID) AND (A.EENDDATE=B.MaxDate)
ORDER BY 1, 2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top