Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...Really appreciate your site. Really good site for learning what others do when they run into problems. You guy's are great!!!..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft SQL Server: Programming Forum

MIN Date Record

thread183-1687238
Read More Threads Like This One
Aidy680 (TechnicalUser)
2 Jul 12 7:51
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 (Programmer)
2 Jul 12 8:47
Hi,

Try something like that:

CODE --> 

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.

http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento

Aidy680 (TechnicalUser)
2 Jul 12 9:14
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 (Programmer)
2 Jul 12 9:48
Do not understand your statement.
Try to adapt the statement shown here to your needs:

CODE --> 

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.

http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento

Aidy680 (TechnicalUser)
2 Jul 12 11:34
Thanks Imex.

I was able to adapt this to read something like:

WITH CTE3 AS...

SELECT ...

FROM ...

INNER JOIN ...

WHERE ...

ORDER BY ...
Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft SQL Server: Programming

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close