Solved

SELECT greatest date

Posted on 2004-09-14
7
888 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now