Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Nroblex
Starting Member
17 Posts |
 Posted - 2007-09-25 : 01:43:11
|
| Hello!I have two tables [person] and [contact]. They are related with person.personId = contact.personId (one to many).In the contact table I have a field named ContactDate (smalldatetime)The question is: How do I write a SQL question to join the two tables, and to get only the latest max(ContactDate) ??Like this: [FirstName, LastName, ContactDate] (and just this row)Thanks in advanceNroblex |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-25 : 02:37:16
|
| [code]SELECT P.FirstName, P.LastName, [ContactDate] = MAX(ContactDate)FROM [person] AS P JOIN [contact] AS C ON C.personId = P.personIdGROUP BY P.FirstName, P.LastName[/code]Kristen |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-25 : 02:50:09
|
For all person, like Kristen showed you, or only the last total contactdate?SELECT TOP 1 WITH TIES p.FirstName, p.LastName, c.ContactDateFROM Person AS pINNER JOIN Contact AS c ON c.PersonID = p.PersonIDORDER BY DATEDIFF(DAY, c.ContactDate, 0)
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Nroblex
Starting Member
17 Posts |
Posted - 2007-09-25 : 03:20:56
|
| Thanks !I finally did it like this:SELECT p.SocialNumber, p.FirstName, p.LastName, (SELECT MAX(c.ContactDate) FROM cContact c WHERE pp.PatientId = c.PatientId AND c.CareUnitId = @CareUnitId) as LatestDate FROM Patients p INNER JOIN PatientInformation AS pp ON pp.PatientId = p.id WHERE (p.SocialNumber LIKE @PersonNr + '%') |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-25 : 03:23:28
|
| I would expect that to be less efficient than a Group By, but that may not be an issue on your volume of data.Kristen |
|
|
|
|
|
|
|
|
A group SQL Select