Maximum Date Value 1

[Taff82]

Hi all,

Not sure how to word this but here goes.

I have a query with three tables as follows:-

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

 

[gavinjb]

You could try using grouping and select the Maximum option for your EENDDATE

 

[scriverb]

Name the above query and substitute the query name for the red code below:

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]

[COLOR=white 006633]MSU Spartan[/color]

 

[Taff82]

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

 

[scriverb]

Okay, give this a try:

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]

[COLOR=white 006633]MSU Spartan[/color]

 

[Taff82]

Hi scriverb,

Nope still no luck.

Taff

 

[PHV]

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

 

[Taff82]

Thanks PHV,

Worked great.

Taff82.