|
|
Posted by Hugo Kornelis on 10/23/30 11:43
On Sat, 25 Mar 2006 08:21:38 +0100, Ryan Dahl wrote:
>>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.
Hi Ryan,
That changes the meaning of the query - the place where you put those
lines dictates what will happen for a kpiID that has only one row.
This one row is by definition the latest - but there's no second latest.
If you use the query I suggested, you'll get this kpiID in your result,
with it's only row as last measurement and NULLs as it's second latest
measurement.
Your version (after moving those rows) will exclude any kpiID with only
one row. Only kpiIDs with two or more measurements will be displayed. If
that is indeed your requirement, then you can safely move these lines.
And you can change the LEFT JOIN in an INNER JOIN as well, to get some
performance gain.
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|