Hi Judah,
I suppose there's more than one way to do this, but this should work...
SELECT a.*
FROM appointment a INNER JOIN
(SELECT patient_id, MIN(appointment_date)
AS appointment_date
FROM appointment
GROUP BY patient_id) b ON a.patient_id =
b.patient_id AND a.appointment_date = b.appointment_date
--Ben
Judah McAuley wrote:
> I'm ill and having difficulty wrapping my head around an issue I know
> I've done before, so I'm hoping that someone can help me out.
>
> I have a table that lists patient's appointments.
>
> Appointment
> ____________
> id
> patient_id
> doctor
> appointment_date (datetime)
>
> A patient can have 0...n appointments on any given day. What I need to
> do right now is generate a view that lists only the first appointment
> for each patient each day. I'm pretty sure I need to be grouping by a
> day of year function on the appointment_date but my brain is kind of
> losing it on the rest. DB server is Sql Server 2000.
>
> Any thoughts?
>
> Thanks in advance,
> Judah
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319764
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
