|
|
Posted by Ryan Dahl on 10/02/13 11:43
On Mon, 27 Mar 2006 23:48:50 +0200, Hugo Kornelis
<hugo@perFact.REMOVETHIS.info.INVALID> wrote:
>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.
Hi Hugo,
thanks for pointing this out. SQLServer accepted without any problems.
As mentioned earlier I tested on MS Access, and it seems that it
doesn't support this join-type (no error-description of any kind) so I
made the mistake of assuming there was a small error in the
sql-string.
Thanks again.
Ryan
Navigation:
[Reply to this message]
|