SELECT		horse_name,
			MAX (DATEPART(YEAR, show_held))
	FROM		horse,
			entry,
			event,
			show
	WHERE		horse.horse_id = entry.horse_id
	AND		entry.event_id = event.event_id
	AND		event.show_id = show.show_id
	GROUP BY	horse.horse_name

---------------------------------------------------------
horse_name   no column name
Boxer	     1995
Daisy	     1996
Flash	     1995
Star	     1996

	SELECT		horse_name,
			MAX (2009 - DATEPART(YEAR, show_held))
	FROM		horse,
			entry,
			event,
			show
	WHERE		horse.horse_id = entry.horse_id
	AND		entry.event_id = event.event_id
	AND		event.show_id = show.show_id
	GROUP BY	horse.horse_name

-----------------------------------------------------
horse_name
Boxer	    14
Daisy  	    14
Flash	    14
Star	    14

I'm supposed to get 14, 13, 14, 13 and why am I getting four 14s?

since there were multiple show_held dates for horse_names, I've used MAX(DATEPART (YEAR, show_held)) to get the latest show_held dates for each horse

then I subtracted them from 2009 to get the number of years between their last competition and 2009. What is wrong in my query?

Hi Kim,

Can you try this?

SELECT        horse_name,
            2009 - MAX(DATEPART(YEAR, show_held))
    FROM        horse,
            entry,
            event,
            show
    WHERE        horse.horse_id = entry.horse_id
    AND        entry.event_id = event.event_id
    AND        event.show_id = show.show_id
    GROUP BY    horse.horse_name

Thanks.

Can you post your sample ddl  of all the tables an some sample data for all the tables ?