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?
 
MartinCMSConnect With a Mentor Commented:
@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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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.