current record

[James]

Can You help me with this one guys. I have a table with patients and dates
of access to a clinic below. Please note patients can have more than one
contacts dates.


ID Name contact date
1 George 01/12/1965
2 George 19/09/1978
3 George 01/02/2003
4 George 11/11/1999
5 Peter 12/11/1961
6 Peter 20/02/2002
7 Zippa 25/03/2003
8 Zippa 01/01/1965
9 Xeon 16/04/1888



What I am trying to do is to select current contact for each patient. for
example, for above table, I need to select the following table:


ID Name contact date
3 George 01/02/2003
6 Peter 20/02/2002
7 Zippa 25/03/2003
9 Xeon 16/04/1888


I have been pondering on a sql solutions for achieving this and so far I
have had no inspiration. Thanks

[David Satz]

something like:
SELECT ID
, Name
, MAX([contact date]) AS [contact date]
FROM table
GROUP BY ID
, Name


--
HTH,
David Satz
Principal Web Engineer
Hyperion Solutions

"James" <james_hector@yahoo.co.uk> wrote in message
news:3e9e8914$1@tnews.web.devx.com...
>
> Can You help me with this one guys. I have a table with patients and
dates
> of access to a clinic below. Please note patients can have more than one
> contacts dates.
>
>
> ID Name contact date
> 1 George 01/12/1965
> 2 George 19/09/1978
> 3 George 01/02/2003
> 4 George 11/11/1999
> 5 Peter 12/11/1961
> 6 Peter 20/02/2002
> 7 Zippa 25/03/2003
> 8 Zippa 01/01/1965
> 9 Xeon 16/04/1888
>
>
>
> What I am trying to do is to select current contact for each patient. for
> example, for above table, I need to select the following table:
>
>
> ID Name contact date
> 3 George 01/02/2003
> 6 Peter 20/02/2002
> 7 Zippa 25/03/2003
> 9 Xeon 16/04/1888
>
>
> I have been pondering on a sql solutions for achieving this and so far I
> have had no inspiration. Thanks
>

[Ben]

try out this one,
Select SeqId, PatientName, VisitDate From Patient
Group By SeqId, PatientName, VisitDate
Having VisitDate = (Select Max(VisitDate) From PAtient A where A.PAtientName
= Patient.PatientName)



"James" <james_hector@yahoo.co.uk> wrote:
>
>Can You help me with this one guys. I have a table with patients and dates
>of access to a clinic below. Please note patients can have more than one
>contacts dates.
>
>
>ID Name contact date
>1 George 01/12/1965
>2 George 19/09/1978
>3 George 01/02/2003
>4 George 11/11/1999
>5 Peter 12/11/1961
>6 Peter 20/02/2002
>7 Zippa 25/03/2003
>8 Zippa 01/01/1965
>9 Xeon 16/04/1888
>
>
>
>What I am trying to do is to select current contact for each patient. for
>example, for above table, I need to select the following table:
>
>
>ID Name contact date
>3 George 01/02/2003
>6 Peter 20/02/2002
>7 Zippa 25/03/2003
>9 Xeon 16/04/1888
>
>
>I have been pondering on a sql solutions for achieving this and so far I
>have had no inspiration. Thanks
>

[Geoff]

Try this.

select p1.name, p1.contact_date
from patient as p1
where contact_date = (select top 1 p2.contact_date from patient as p2 where
p1.name = p2.name order by p2.contact_date desc)

as an example for George, the second select will pull off only that entry
which is the top 1 of all the George's ordered by contact_date in descending
order (newest to oldest) which should be 01/02/2003. it will choose only
that one from p1 to create your initial select table by comparing the contact_dates.


gg


"James" <james_hector@yahoo.co.uk> wrote:
>
>Can You help me with this one guys. I have a table with patients and dates
>of access to a clinic below. Please note patients can have more than one
>contacts dates.
>
>
>ID Name contact date
>1 George 01/12/1965
>2 George 19/09/1978
>3 George 01/02/2003
>4 George 11/11/1999
>5 Peter 12/11/1961
>6 Peter 20/02/2002
>7 Zippa 25/03/2003
>8 Zippa 01/01/1965
>9 Xeon 16/04/1888
>
>
>
>What I am trying to do is to select current contact for each patient. for
>example, for above table, I need to select the following table:
>
>
>ID Name contact date
>3 George 01/02/2003
>6 Peter 20/02/2002
>7 Zippa 25/03/2003
>9 Xeon 16/04/1888
>
>
>I have been pondering on a sql solutions for achieving this and so far I
>have had no inspiration. Thanks
>