Re: Select latest record from join
From: Paul (zammit_joe_at_hotmail.com)
Date: 05/25/04
- Next message: Sergei Almazov: "Re: text search on stored procedures"
- Previous message: VikramKamath: "Re: Wierd results"
- In reply to: John Bell: "Re: Select latest record from join"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 25 May 2004 12:09:33 +0100
Cheers John, That seemed to do the trick. The reason i couldn't ignore the
call date is because the user can change it. I.e. they could put a note in
for a historic call, which would mean it would have a higher callid, even
though it was in the past. Thanks again for all your help.
Paul
"John Bell" <jbellnewsposts@hotmail.com> wrote in message
news:OK3DP1jQEHA.624@TK2MSFTNGP11.phx.gbl...
> 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>
- Next message: Sergei Almazov: "Re: text search on stored procedures"
- Previous message: VikramKamath: "Re: Wierd results"
- In reply to: John Bell: "Re: Select latest record from join"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
