Re: Select latest record from join

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: John Bell (jbellnewsposts_at_hotmail.com)
Date: 05/25/04 

Date: Tue, 25 May 2004 10:47:33 +0100

Hi

This is more complex as you have not posted DDL and example data. It seems
that you are not retaining a time with your date which would mean that the
original query should have been ok to 3/100 sec. You don't say if the
telephonecallid is cycled or incremental, if it was an identity you could
probably ignore the call date totally.
The following looks horendous and is unchecked!!

select c.*, U.*
>From Candidates C left outer join
(
select CandidateID, Max (CallDate) As MaxCallDate,
>From TelephoneCalls
Group By CandidateID
) As T On C.CandidateID = T.CandidateID
Left Outer Join
( select CandidateID, CallDate, MAX(telephonecallid) AS Maxtelephonecallid
>From TelephoneCalls
Group By CandidateID, CallDate
) As S On C.CandidateID = S.CandidateID AND S.CallDate = T.MAxCallDate
Left Outer Join
TelephoneCalls U On C.CandidateID = S.CandidateID AND S.CallDate =
T.MAxCallDate AND S.Maxtelephonecallid = U.telephonecallid

John

"Paul" <zammit_joe@hotmail.com> wrote in message
news:%23jEypOjQEHA.3476@tk2msftngp13.phx.gbl...
> Hi John
>
> This does retrieve the max call date with the candidate, but I now want to
> retrieve other information from the telephonecalls table i.e. notes etc.
If
> I now join this to the telephone calls I will get 2 rows if 2 phone calls
> were made on the same day. In this case, i would like to select the SINGLE
> row with the greatest telephonecallid (Which i can assume is the most
> recently entered call log).
>
> Thanks John
>
> "John Bell" <jbellnewsposts@hotmail.com> wrote in message
> news:%231Z0iIjQEHA.2876@TK2MSFTNGP09.phx.gbl...
> > Hi
> >
> > This untested but you could try:
> >
> > SELECT C.*, ( SELECT Max(CALLDATE) FROM TelephoneCalls T WHERE
> T.CandidateId
> > = C.CandidateId ) AS MaxCallDate
> > FROM Candidates C
> >
> > John
> >
> > "Paul" <zammit_joe@hotmail.com> wrote in message
> > news:ew5x9DjQEHA.1440@TK2MSFTNGP10.phx.gbl...
> > > Hi All
> > >
> > > I have 2 tables (one is candidates, the other is telephone calls) and
i
> > want
> > > to join them to show a row for every candidate joined with the latest
> > phone
> > > conversation. I am currently doing it like this:
> > >
> > > select * From Candidates left outer join
> > > (
> > > select CandidateID, Max (CallDate) As MaxCallDate
> > > From TelephoneCalls
> > > Group By CandidateID
> > > ) As Tbl1 On Candidates.CandidateID = Tbl1.CandidateID Left Outer Join
> > > TelephoneCalls On Tbl1.MaxCallDate = TelephoneCalls.CallDate
> > >
> > > However, if 2 telephone calls were stored on the same date then this
> > returns
> > > 2 rows for that particular candidate. In this case, i would like to
only
> > > return the row with the greatest [TelephoneCallID] which i can assume
is
> > the
> > > last conversation with that candidate. I think i am almost there, but
> any
> > > help would be appreciated.
> > > Thanx in advance
> > > Paul
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Select latest record from join
    ... Cheers John, That seemed to do the trick. ... > From Candidates C left outer join ... > select CandidateID, Max As MaxCallDate, ... > (select CandidateID, CallDate, MAX(telephonecallid) AS Maxtelephonecallid ...
    (microsoft.public.sqlserver.programming)
  • Re: Select latest record from join
    ... FROM TelephoneCalls T WHERE T.CandidateId ... John ... > select * From Candidates left outer join ... > select CandidateID, Max As MaxCallDate ...
    (microsoft.public.sqlserver.programming)
  • Re: Select latest record from join
    ... This does retrieve the max call date with the candidate, ... retrieve other information from the telephonecalls table i.e. notes etc. ... Thanks John ...
    (microsoft.public.sqlserver.programming)