TOP 1 in INNER JOIN

[Guest]

SELECT DISTINCT C1.SKU, P.SKU_Desc, C1.COST_ExW, C2.COST_ExW, C1.REGION,
C2.Cost_ExW-C1.cost_ExW AS CostDiff, Format(C1.EFF_DATE,"mm-dd-yyyy") AS
Expr1, C2.EFF_DATE
FROM (COST_SKU AS C1 INNER JOIN ALLPRODUCT AS P ON C1.SKU=P.SKU) INNER JOIN
COST_SKU AS C2 ON (C2.EFF_DATE< C1.EFF_DATE) AND (C2.SKU=C1.SKU)
WHERE C1.REGION="NA" AND C2.REGION="nile" AND C2.SKU=P.SKU
ORDER BY Format(C1.EFF_DATE,"mm-dd-yyyy") DESC;


i want to select only the first record from C2 where C2.EFF_DATE<C1.EFF_DATE
how can i do that? help!

 

[Jeff L]

Select TOP 1 <rest of your statement>

 

[Guest]

thanks for the reply jeff but that doesnt work. I m trying to select the
first date which is less than the date in C1 table

here is what it think is going on using this query:

c1.eff_date | c2.eff_date
7/1/06 6/2/06
7/1/06 2/2/06

so c1.eff_date is compared to all the dates in c2 where c2.eff_date is less
than 7/1/06. I just want the first date less than c1.eff_date to be compared
so i was thinking if i can somehow get only the first record from c2 , that
will solve the problem. This is what itried next:

SELECT DISTINCT C1.SKU, P.SKU_Desc, C1.COST_ExW, C2.COST_ExW, C1.REGION,
C2.Cost_ExW-C1.cost_ExW AS CostDiff, Format(C1.EFF_DATE,"mm-dd-yyyy") AS
Expr1, C2.EFF_DATE
FROM (COST_SKU AS C1 INNER JOIN ALLPRODUCT AS P ON C1.SKU=P.SKU) INNER JOIN
[SELECT TOP 1 * FROM COST_SKU ORDER BY EFF_DATE] AS C2 ON (C2.SKU=C1.SKU)
AND (C2.EFF_DATE<C1.EFF_DATE)
WHERE C1.REGION="NA" AND C2.REGION="NA" AND C2.SKU=P.SKU
ORDER BY Format(C1.EFF_DATE,"mm-dd-yyyy") DESC;

note the TOP 1 in the derived C2 table but that doesnt return any records!
if i take out the TOP 1 it behaves like the old query.