Solved

SELECT greatest date

Posted on 2004-09-14
7
894 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

717 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