MIN Date Record

[Aidy680]

Hi,

I have a query with 3 tables in it. It contains numerous fields, mainly from Table1, with a sprinkling from Table2 and Table3. The query returns 5 records.

The records are all the same apart from the Date field, which contains different values for each of the records. The Date field is in Table3.

I want to return the record with the earliest date, but not sure how too.

I think I need to use a GROUP BY but I dont want to group on all the different fields.

If I use a MIN in the WHERE clause, around the Date field, I get a:

"An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list..."

Any help would be appreciated!

 

[imex]

Hi,

Try something like that:

with CTE as
(
    select t1.*, t2.* t3.*, ROW_NUMBER() OVER(ODER BY DateColumn) as RN
    from Table1 as t1
    join Table2 as t2 on t2.ID = t1.ID
    join Table3 as t3 on t3.ID = t1.ID
)

select * from CTE
where RN = 1

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[Aidy680]

Thanks imex.

I thought it might be useful (understatement I know) to post the code:



(SELECT TOP (100) PERCENT dbo.Table3.sdtDateTimeStamp, dbo.Table1.intFXInstructionID, Table1.vchFundCode,
dbo.Table1.chCCY, dbo.Table1.monAmount,
dbo.Table1.vchWindowsLogin, dbo.Table1.vchContactEmail,
dbo.Table1.sdtDateTimeRecieved, dbo.Table1.vchSpecialInstruction, dbo.Table1.vchGroupType,
dbo.Table1.vchAcceptWinLogin,
dbo.Table2.intFXDealID, dbo.Table2.vchTT,
dbo.Table2.monBaseAmount, dbo.Table2.decRate,
dbo.Table2.sdtValueDate AS FXsdtValueDate, dbo.Table2.vchBankCode, dbo.Table2.vchDealer,
dbo.Table2.sdtDealtDateTime, dbo.Table2.sdtDateTimeAdded, dbo.Table2.vchGroupType AS FXvchGroupType,
dbo.Table2.vchAcceptInsWindLogin,dbo.Table3.intFXRateGroupID,dbo.Table3.vchBankCode
FROM dbo.Table3 RIGHT OUTER JOIN
dbo.Table3 AS Table3_1 ON dbo.Table3.vchBankCode = Table3_1.vchBankCode AND
dbo.Table3.intFXRateGroupID = Table3_1.intFXRateGroupID
RIGHT OUTER JOIN
dbo.Table1
INNER JOIN
dbo.Table2 ON dbo.Table1.intFXInstructionID = dbo.Table2.intFXInstructionID ON
Table3_1.intFXRateID = dbo.Table2.intFXRateID) A

LEFT JOIN

(SELECT intFXRateGroupID, MIN(sdtDateTimeStamp), vchBankCode
FROM Table3
GROUP BY intFXRateGroupID, vchBankCode) B

ON A.sdtDateTimeStamp = B.sdtDateTimeStamp
AND A.intFXRateGroupID = B.intFXRateGroupID
AND A.vchBankCode = B.vchBankCode

WHERE (CONVERT(varchar(11), dbo.Table2.sdtDealtDateTime, 111) = CONVERT(varchar(11), '2012/01/09', 111)) AND
(dbo.Table1.vchFundCode = 'DUMMY') AND (dbo.Table1.intFXInstructionID = 99999)

ORDER BY dbo.Table3.sdtDateTimeStamp

Dont think I'm far off....

 

[imex]

Do not understand your statement.
Try to adapt the statement shown here to your needs:

with CTE3 as
(
    SELECT intFXRateGroupID, MIN(sdtDateTimeStamp) as MinSdtDateTimeStamp, vchBankCode
    FROM Table3
    GROUP BY intFXRateGroupID, vchBankCode
)

select t1.*, CTE3.MinSdtDateTimeStamp
from Table1 as t1
join Table2 as t2 on t2.intFXInstructionID = t1.intFXInstructionID 
left join CTE3 on (CTE3.intFXRateGroupID = t2.intFXRateGroupID) and
                  (CTE3.vchBankCode = t2.vchBankCode)

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[Aidy680]

Thanks Imex.

I was able to adapt this to read something like:

WITH CTE3 AS...

SELECT ...

FROM ...

INNER JOIN ...

WHERE ...

ORDER BY ...