Help with query

• kevin77

  SSCarpal Tunnel

  Points: 4041

  More actions

  February 25, 2004 at 7:32 am

  #107668

  Suppose we have a table with 3 columns, personID, eventTime, and eventDescription.  Let's say that people only generate about 3 events per day.  I need a query that will return the first event for each person for each day.  For example, suppose we have the following data:

  74, 2/20/2004 9:00:00 AM, 'Arrive work'

  32, 2/20/2004 9:05:00 AM, 'Arrive work'

  74, 2/20/2004 12:30:00 PM, 'Lunch'

  32, 2/20/2004 12:00:00 PM, 'Lunch'

  32, 2/20/2004 11:00:00 PM, 'Bed'

  32, 2/21/2004 9:01:00 AM, 'Arrive work'

  74, 2/21/2004 9:00:00 AM, 'Wake up'

  74, 2/21/2004 12:00:00 PM, 'Lunch'

   

  The query should return the following data:

  74, 2/20/2004 9:00:00 AM, 'Arrive work'

  32, 2/20/2004 9:05:00 AM, 'Arrive work'

  74, 2/21/2004 9:00:00 AM, 'Wake up'

  32, 2/21/2004 9:01:00 AM, 'Arrive work'

   

  The following query will return the personID and eventTime, but I don't know how to get the eventDescription.

  SELECT personID, MIN(eventTime)

  FROM         Events

  GROUP BY personID, CONVERT(char(10), eventTime, 101)

  ORDER BY personID

  If I add the eventDescription column to the above query it will cause an error because it is not contained in either an aggregate function or the GROUP BY clause.  This makes sense, but I don't know how to get the results I want.

  Thanks in advanced for any help.

• noeld

  SSC Guru

  Points: 96590

  More actions

  February 25, 2004 at 8:06 am

  #495568

  SELECT *

  FROM  Events E

   JOIN

   (

  SELECT personID, MIN(eventTime)

  FROM         Events

  GROUP BY personID, CONVERT(char(10), eventTime, 101)

  ) EMin On E.PersonID = Emin.PersonID and E.EventTime = EM.EventTime

  ORDER BY personID

  
  * Noel