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

[Judah McAuley]

That is a bizarre piece of trivia Ken, thanks.

Here's the solution I eventually came up with (courtesy of help of a
couple folks):

SELECT     a.*
FROM    appointment a INNER JOIN
      (SELECT     patient_id, MIN(appointment_date) AS appointment_date
               FROM   appointment
               GROUP BY patient_id, convert(varchar(10),
appointment_date, 101)) b
ON a.patient_id = b.patient_id
AND a.appointment_date = b.appointment_date
ORDER BY a.patient_id, a.appointment_date

The trouble I was having was remembering to join the table back
against itself as a derived table and apply the group by inside the
derived table. SQL can be a beast when your head is stuffed up.

Cheers,
Judah



On Tue, Feb 24, 2009 at 7:22 PM, Ken Schaefer <Ken at adopenstatic.com> wrote:
> SQL Server 2000 stores datetime fields as two numbers - one for days since 1/1/1900 and one for the number of milliseconds since midnight. Casting to int seems to drop the time part...
>
> Cheers
> Ken