Get max(date) rows

• Saturday, January 12, 2013 7:03 PM

   

   
  

  0

  Table [events] contains the high level version of events that an organizer produces

  Table [eventlisting] holds the individual listings for the event for each year. This table includes the items that will change from one year to the next.

  At the beginning of the season, the organizer can elect to copy the previous years eventlisting - to do this he supplies the new event date, and the stored procedure inserts the previous years data into a new row with the new date. So, when presented with the Copy interface, the organizer selects an event from the [event] table. (Organizer may have more than one event and an event may include more than 1 listing - EVENT 1 RACE (5k Fun Run, Marathon). This selection populates the next list  event listings. Here is my question. The listings list should show all of the listings associated with the parent event, but only the most recent occurance. ( EventID 1 >> SUBEVENT 1 9/9/2011, SUBEVENT1 9/10/2012, SUBEVENT 2 9/9/2011, SUBEVENT 2 9/10/2012) Although there are 4 SUBEVENTS that could be selected I only want the most recent. This select only brings back the one listing that has the max date. I need both of the listings but only one of each.

  SELECT e.eventlistid 
  , e.name
  FROM eventlisting e
  WHERE e.eventid = @EVENTID
  AND e.datestart = (select MAX(datestart) FROM eventlisting where eventid = @EVENTID AND eventlistid = e.eventlistid)

  
  
  • Reply
  • Quote