Saturday, January 12, 2013 7:03 PM
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)
Saturday, January 12, 2013 7:15 PM
I'm clear over what you want back. I would guess that you want both subevents from yesteryear, that is SUBEVENT1 9/10/2012 and SUBEVENT 2 9/10/2012. But in such case, why yoddle with MAX. Why not just e.datestart BETWEEN '20120101' AND '20121231'?
Erland Sommarskog, SQL Server MVP, firstname.lastname@example.org
Sunday, January 13, 2013 5:20 PMModeratorCan you please post DDL of both tables, some input data and desired result based on input. Your narrative is not 100% clear. I suspect you need to use ROW_NUMBER() approach to get the latest listed event for event, but somehow I am not certain it is what you want.
For every expert, there is an equal and opposite expert. - Becker's Law