Another ForEach Imitation

We have a posting for News & Announcements. There are multiple announcement types and multiple regions. I'm attempting to create an sProc that will allow me to get the most recent announcement inserted into a table for every region of each announcetype. I was original just grabbing the TOP 10* with ORDER BY DESC, but now they want one announcement for each announcetype for every region. The Statement needs to be dynamic since the number of announcetype's will change and the number of regions may change as well.

All I have now, after changing things around 100 times, is:


AS



BEGIN

DECLARE @ONE TABLE(

AnnounceType VARCHAR (100)

, RegionName VARCHAR (20)

, Title VARCHAR (500)

, URL VARCHAR (250)

)

INSERT INTO @ONE

SELECT DISTINCT AT.AnnounceType, MAX (REG.RegionName) RegionName, HJA.Title, HJA.URL

FROM dbo.HotJobAnnouncements HJA

JOIN dbo.AnnounceType AT

ON HJA.AnnounceTypeID = at.AnnounceTypeID

JOIN CORE.dbo.Regions REG

ON HJA.Region = REG.Region

GROUP BY AT.AnnounceType, HJA.Title, HJA.URL

SELECT * FROM @ONE

ORDER BY AnnounceType, RegionName, Title

END

If anyone can help me or point me in the right direction I will appreciate it.