Prev: Interested in SQL Server Audit Tool
Next: Distinct issues
From: Gert-Jan Strik on 21 Apr 2010 06:06
Like this?

SELECT I.ID, ThreadUserFrom, ThreadUserTo, vSubject, vMessage,
vThreadMessage, T.dCreateDate
FROM tblInbox AS I
JOIN tblInboxThread AS T
ON I.ID = T.iInboxThread
WHERE T.dCreateDate = (SELECT MAX(B.dCreateDate)
FROM tblInboxThread AS B
WHERE B.iInboxThread = I.ID);

--
Gert-Jan

skywalker skywalker wrote:
>
> Dear Plamen,
>
> The result is not correct.
>
> Regards,
> Sky
>
> On Apr 21, 9:26 am, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> > On SQL Server 2000 you can try the following (note it may produce multiple rows for ID if there are max create date
> > values that are the same):
> >
> > SELECT I.ID, ThreadUserFrom, ThreadUserTo, vSubject, vMessage, vThreadMessage, I.dCreateDate
> > FROM tblInbox AS I
> > JOIN tblInboxThread AS T
> > ON I.ID = T.iInboxThread
> > WHERE I.dCreateDate = (SELECT MAX(B.dCreateDate)
> > FROM tblInbox AS B
> > WHERE B.ID = I.ID);
> >
> > --
> > Plamen Ratchevhttp://www.SQLStudio.com
From: Plamen Ratchev on 21 Apr 2010 10:47
What is incorrect? If I understood correct you need the latest from table tblInbox. If you need the latest based on the
date from the other table then the change made by Gert-Jan will do it.

--
Plamen Ratchev
http://www.SQLStudio.com
First  |  Prev  | 
Pages: 1 2
Prev: Interested in SQL Server Audit Tool
Next: Distinct issues