SELECT greatest date

OK...I have two tables (Projects & Highlights).

Table Projects
==========
ID     Project Name
--------------------------
1      Testing ABC
2      Again and Again
3      Blah Blah Blah


Table Highlights
==============
ID       ProjectID      HighlightDate              Comments
--------------------------------------------------------------------
1              2             9/9/2004            This is a test
2              2             9/2/2004            Bklah
3              2             9/17/2004            One more Time
4              1            9/9/2004            This is a test
5              1             9/14/2004            Again and Again


Now....I want to select ALL the Projects from the Project table AND select a highlight from the highlight table if there is one, but I only want the NEWEST one or the one highlight with the greatest date.

Any help?

I have tried doing the TOP 1 or MAX or WHERE HighlightDate = Max(HighlightDate) and that didn't work.

Thanks!
Dark Magneto
darkmagnetoAsked:
Who is Participating?
 
MartinCMSCommented:
@darkmagneto --- try this.... should work for you!

select h.ProjectID,p.projectname, h.HighlightDate, h2.Comments
from Projects p
left outer join (select ProjectID, max(HighlightDate) HighlightDate from Highlights group by projectid) h
   on p.id = h.ProjectID  
       join Highlights h2 on p.id = h2.ProjectID and h.HighlightDate = h2.HighlightDate
where h.ProjectID is not null
0
 
bobbit31Commented:
something like this maybe?

SELECT ProjectName, Max(HighlightDate) FROM projects
left join highlights on projects.ID = highlights.projectid
GROUP BY highlights.projectid, projects.projectname
0
 
darkmagnetoAuthor Commented:
Nope...didn't work
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jay ToopsCommented:
SELECT ProjectName, Max(cast (HighlightDate as datetime)) FROM projects
left join highlights on projects.ID = highlights.projectid
GROUP BY highlights.projectid, projects.projectname

Jay
0
 
xxg4813Commented:
select p.projectname, h.highlightDate
from projects p
left outer join (select projectid, max(highlightDate) highlightDate from highlight group by projectid) h
   on p.projectid = h.project id

It should work!



0
 
Mike_MetroCommented:
SELECT *
FROM Projects P
LEFT OUTER JOIN Highlights H
ON P.ID = H.ProjectID
LEFT OUTER JOIN (
      SELECT ProjectID, Max(HighlightDate) as MaxDate
      FROM Hightlights
      Group By ProjectID
) HMax
ON H.ProjectID = HMax.ProjectID and H.HighlightDate = HMax.MaxDate
0
 
darkmagnetoAuthor Commented:
xxg4813,

OK...I tried your solution and it works....kind of.

It will give me the Highlight ID and the Date perfect.  I also need the Comments field with it.  So when I try and add this it tells me I need it in the GROUP BY clause and then I put it there and then I am back where I started.  

Any suggestions?

So my final output I need

PROJECT ID           PROJECT NAME                    HIGHLIGHT DATE(GREATEST)                   COMMMENT
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.