Select Most Recent date

Question

SELECT     dbo.Team.ID, dbo.Team.Comment, dbo.Team.Date, dbo.TeamName.Name, dbo.Contacts.ContactName 
FROM       dbo.Team 
INNER JOIN dbo.TeamName ON dbo.Team.ID = dbo.TeamName.id 
INNER JOIN dbo.Contacts ON dbo.Team.ContactID = dbo.Contacts.ContactID   
WHERE      dbo.TeamName.idstatus = 'Active'

I am trying to query it in a way that it only gives me records with most recent date but it is getting little confusing because I am already pulling data from 3 tables and not sure how to use MAX(date) here.

Can you show some sample data and desired results? What you're trying to do is a little confusing to us, too. Are you trying to get one row per team? What does `Team.Date` represent - `Date` of WHAT? Shouldn't you name your columns a little better? Also why do you store team name in a separate table? — Aaron Bertrand, Oct 08 '13 at 21:31

score 1 · Accepted Answer · answered Oct 08 '13 at 21:57

SELECT     dbo.Team.ID, dbo.Team.Comment, MAX(dbo.Team.Date) LatestDate, dbo.TeamName.Name, dbo.Contacts.ContactName 
FROM       dbo.Team 
INNER JOIN dbo.TeamName ON dbo.Team.ID = dbo.TeamName.id 
INNER JOIN dbo.Contacts ON dbo.Team.ContactID = dbo.Contacts.ContactID   
WHERE      dbo.TeamName.idstatus = 'Active'
GROUP BY   dbo.Team.ID, dbo.Team.Comment, dbo.TeamName.Name, dbo.Contacts.ContactName

ADDING MAX() Function to your date column and group by all the columns with you the Latest date for the combination of values in all other columns.

score 0 · Answer 2 · answered Oct 08 '13 at 21:26

0

I'm assuming you want to filter by dbo.Team.Date.

Change your WHERE clause to:

WHERE (dbo.TeamName.idstatus = 'Active') 
and (dbo.Team.Date=(select max(Date) from dbo.Team))

answered Oct 08 '13 at 21:26

geomagas

3,230
1
17
27

score 0 · Answer 3 · answered Oct 08 '13 at 21:31

Use GroupBy and Having:

SELECT     dbo.Team.ID, dbo.Team.Comment, MAX(dbo.Team.Date), dbo.TeamName.Name,   dbo.Contacts.ContactName, 
FROM         dbo.Team 
INNER JOIN
dbo.TeamName ON dbo.Team.ID = dbo.TeamName.id 
INNER JOIN
dbo.Contacts ON dbo.Team.ContactID = dbo.Contacts.ContactID
WHERE     (dbo.TeamName.idstatus = 'Active')
GROUP BY dbo.Team.ID, dbo.Team.Comment, dbo.Team.Date, dbo.TeamName.Name, dbo.Contacts.ContactName
HAVING dbo.Team.Date = MAX(dbo.Team.Date)

Select Most Recent date

3 Answers3