Question about Grouping rows in Select statements with joins

Hi
I am trying to use the select statement to return rows which have a MAX
date and then associate the date with a Title field in the #c table. At
the moment when I add the Title field to the Select list I am getting
the latest date for each Title. How do I exclude the Title field form
the GROUP BY clause but display it with the date in the select list?
/* correct number of records but without the required Title field */
CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
CREATE TABLE #c(ContentID int,Title varchar(100))
INSERT INTO #c VALUES (1,'First Item')
INSERT INTO #c VALUES (2,'Second Item')
INSERT INTO #h VALUES (10,1,'20050301')
INSERT INTO #h VALUES (10,2,'20050302')
INSERT INTO #h VALUES (11,1,'20050304')
INSERT INTO #h VALUES (11,2,'20050303')
SELECT #h.EmailID, MAX(#h.DateStamp)
FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
GROUP BY #h.EmailID
DROP TABLE #h
DROP TABLE #c
GO
/* adding the Title field gives me extra rows. I only want the
Title associated with the latest date */
CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
CREATE TABLE #c(ContentID int,Title varchar(100))
INSERT INTO #c VALUES (1,'First Item')
INSERT INTO #c VALUES (2,'Second Item')
INSERT INTO #h VALUES (10,1,'20050301')
INSERT INTO #h VALUES (10,2,'20050302')
INSERT INTO #h VALUES (11,1,'20050304')
INSERT INTO #h VALUES (11,2,'20050303')
SELECT #h.EmailID, MAX(#h.DateStamp), #c.Title
FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
GROUP BY #h.EmailID, #c.Title
DROP TABLE #h
DROP TABLE #c
GO
Cheers
j055try
CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
CREATE TABLE #c(ContentID int,Title varchar(100))
INSERT INTO #c VALUES (1,'First Item')
INSERT INTO #c VALUES (2,'Second Item')
INSERT INTO #h VALUES (10,1,'20050301')
INSERT INTO #h VALUES (10,2,'20050302')
INSERT INTO #h VALUES (11,1,'20050304')
INSERT INTO #h VALUES (11,2,'20050303')
select email, max_date, title from (
SELECT #h.EmailID email, MAX(#h.DateStamp) max_date
FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
GROUP BY #h.EmailID
) t
inner join #h on #h.EmailID=t.email and #h.DateStamp = t.max_date
inner join #c on #h.ContentID=#c.ContentID
DROP TABLE #h
DROP TABLE #c
GO
"j055" wrote:

> Hi
> I am trying to use the select statement to return rows which have a MAX
> date and then associate the date with a Title field in the #c table. At
> the moment when I add the Title field to the Select list I am getting
> the latest date for each Title. How do I exclude the Title field form
> the GROUP BY clause but display it with the date in the select list?
>
> /* correct number of records but without the required Title field */
> CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
> CREATE TABLE #c(ContentID int,Title varchar(100))
> INSERT INTO #c VALUES (1,'First Item')
> INSERT INTO #c VALUES (2,'Second Item')
> INSERT INTO #h VALUES (10,1,'20050301')
> INSERT INTO #h VALUES (10,2,'20050302')
> INSERT INTO #h VALUES (11,1,'20050304')
> INSERT INTO #h VALUES (11,2,'20050303')
> SELECT #h.EmailID, MAX(#h.DateStamp)
> FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
> GROUP BY #h.EmailID
> DROP TABLE #h
> DROP TABLE #c
> GO
> /* adding the Title field gives me extra rows. I only want the
> Title associated with the latest date */
> CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
> CREATE TABLE #c(ContentID int,Title varchar(100))
> INSERT INTO #c VALUES (1,'First Item')
> INSERT INTO #c VALUES (2,'Second Item')
> INSERT INTO #h VALUES (10,1,'20050301')
> INSERT INTO #h VALUES (10,2,'20050302')
> INSERT INTO #h VALUES (11,1,'20050304')
> INSERT INTO #h VALUES (11,2,'20050303')
> SELECT #h.EmailID, MAX(#h.DateStamp), #c.Title
> FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
> GROUP BY #h.EmailID, #c.Title
> DROP TABLE #h
> DROP TABLE #c
> GO
> Cheers
> j055
>