Solved

SELECT greatest date

Posted on 2004-09-14
7
890 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:darkmagneto
7 Comments
 
LVL 18

Expert Comment

by:bobbit31
ID: 12059080
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
 

Author Comment

by:darkmagneto
ID: 12059171
Nope...didn't work
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12059241
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 4

Expert Comment

by:xxg4813
ID: 12059248
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
 
LVL 6

Expert Comment

by:Mike_Metro
ID: 12059292
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
 

Author Comment

by:darkmagneto
ID: 12064808
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
 
LVL 8

Accepted Solution

by:
MartinCMS earned 250 total points
ID: 12065110
@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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question