Posted by Ryan Dahl on 03/25/06 09:21

Hi Hugo,

Thanks a lot. I got them both working without any hassle.

>SELECT a.kpiID, a.periodID, a.Actual
>FROM table1 AS a
>WHERE (SELECT COUNT(*)
> FROM table1 AS b
> WHERE b.kpiID = a.kpiID
> AND b.periodID >= a.periodID) <= 2

I find this to be quite clever - had to look at it some time to figure
out how it works.

>>
>>Preferably I would like the final result to be a View with the
>>following fields:
>>kpiID, periodID_newest, Actual_newest, periodID_sec_newest,
>>Actual_sec_newest
>
>In that case, try this instead:
>
>SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual
>FROM table1 AS a
>LEFT JOIN table1 AS b
> ON b.kpiID = a.kpiID
> AND b.periodID = (SELECT MAX(c.periodID)
> FROM table1 AS c
> WHERE c.kpiID = a.kpiID
> AND c.periodID < a.periodID)
>WHERE a.periodID = (SELECT MAX(t.periodID)
> FROM table1 AS t
> WHERE t.kpiID = a.kpiID)
>
Works as well - minor adjustment needed: Move lines 5-8 to the end.

Regards
Ryan

[Back to original message]