"adam" wrote:

> Hi Expert,
>
> I came across a situation where the query result returns all non-NULL
> records even I use TOP 1 statement. Here is the SQL statement:
>
> SELECT CONTRACT_NUMBER, STEP, STATUS_END_DATE, CIS_PK
> FROM dbo.[VIEW1] T1
> WHERE (CONTRACT_NUMBER = 'S07-123A' OR
> CONTRACT_NUMBER = 'S07-127A' OR
> CONTRACT_NUMBER = 'S07-129A') AND (STEP =
> (SELECT TOP 1 STEP
> FROM dbo.[VIEW2] T2
> WHERE T2.CIS_PK = T1.CIS_PK AND
> T2.STATUS_END_DATE IS NOT NULL
> ORDER BY STEP ASC))
>
> For the above SQL statement, "CIS_PK is key". "STEP" is unique within each
> CONTRACT_NUMBER group.
>
> The incorrect result that I got is:
> CONTRACT_NUMBER STEP DATE
> CIS_PK
> S05-137A 4 8/11/2006
> 728
> S05-137A 7 10/1/2006
> 731
>
> I am expecting the right answer, which is:
> S05-137A 4 8/11/2006
> 728
>
> Thanks,
>