[thelist] SQL: Select first item for each person for each day

[jft]

Judah,
Try:
  select a1.id, a1.patient_id, a1.doctor, a1.appointment_date
     from Appointment a1
  where a1.appointment_date  = 
      (select min(a2.appointment_date)
         from Appointment a2
       where a2.patient_id = a1.patient_id
          and cast(a2.appointment_date as date) = cast(a1.appointment_date as date)) 
     and cast(a1.appointment_date as date) = '25 February 2009'
HTH
John
>  -------Original Message-------
>  From: Judah McAuley <judah at wiredotter.com>
>  Subject: [thelist] SQL: Select first item for each person for each day
>  Sent: 25 Feb '09 05:20
>  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.
>  Thanks in advance,
>  Judah