Searching for the most recently dated record

Is there a way to only pull the record with the most recent date if there are several similar records in the table?

For example,

I have two tables, Candidates and Candidate_Activity.  I'm looking for the date of an activity in the CA table.  There may be many activities of the type I'm searching for, but I only need to know the date of the most recent one.

My basic search is:

SELECT      CA.Entry_Date AS Date, C.Candidate_Id, C.First_Name, C.Last_Name,
                        C.Phone_1 AS Phone, C.Email_Address AS Email
FROM          dbo.Candidates C INNER JOIN
                        dbo.Candidate_Activity CA ON C.Candidate_Id = CA.Candidate_Id
WHERE      (CA.Act_Type = 'LT App Sig')
ORDER BY CA.Entry_Date DESC, C.Last_Name, C.First_Name

But that could/would pull all the LT App Sig activities for each Candidate.  There may be one dated 4/23/2001, another dated 3/31/2005, another dated 6/20/2012.  I only want the most recent date.

Does that make sense?  Can it be done?  

Thanks!

PS...my db is an .adp
LVL 1
fabi2004CIOAsked:
Who is Participating?
 
ralmadaCommented:
small issue there: drop the AS Date

SELECT  max(CA.Entry_Date) as EntryDate,
      C.Candidate_Id,
      C.First_Name,
      C.Last_Name,
        C.Phone_1 AS Phone,
      C.Email_Address AS Email
FROM          dbo.Candidates C
INNER JOIN dbo.Candidate_Activity CA ON C.Candidate_Id = CA.Candidate_Id
WHERE      (CA.Act_Type = 'LT App Sig')
group by C.Candidate_Id,
      C.First_Name,
      C.Last_Name,
        C.Phone_1 AS Phone,
      C.Email_Address AS Email
ORDER BY EntryDate DESC, C.Last_Name, C.First_Name
0
 
ralmadaCommented:
the below will work both in Access and SQL server:
SELECT      CA.maxEntry_date AS Date, C.Candidate_Id, C.First_Name, C.Last_Name, 
                        C.Phone_1 AS Phone, C.Email_Address AS Email
FROM          dbo.Candidates C INNER JOIN
                        (select Candidate_ID, max(Entry_Date) maxEntry_date from dbo.Candidate_Activity group by Candidate_ID) CA ON C.Candidate_Id = CA.Candidate_Id
WHERE      (CA.Act_Type = 'LT App Sig')
ORDER BY CA.Entry_Date DESC, C.Last_Name, C.First_Name

Open in new window

0
 
ralmadaCommented:
If not, in SQL server 2005 or above you can use row_number

select * from (
	SELECT      CA.Entry_Date AS Date, C.Candidate_Id, C.First_Name, C.Last_Name, row_number() over (partition by C.Candidate_ID order by CA.Entry_Date desc) rn
        	                C.Phone_1 AS Phone, C.Email_Address AS Email
	FROM          dbo.Candidates C INNER JOIN
        	                dbo.Candidate_Activity CA ON C.Candidate_Id = CA.Candidate_Id
	WHERE      (CA.Act_Type = 'LT App Sig')
) a
where rn = 1 
ORDER BY Entry_Date DESC, Last_Name, First_Name

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
fabi2004CIOAuthor Commented:
I get two errors:

ADO error:  Invalid column name 'Entry_Date', Invalid column name 'Act_Type'

ps.  I don't have SQL 5 or higher
0
 
ralmadaCommented:
correction to the first query:

SELECT      CA.maxEntry_date AS Date, C.Candidate_Id, C.First_Name, C.Last_Name,
                        C.Phone_1 AS Phone, C.Email_Address AS Email
FROM          dbo.Candidates C INNER JOIN
                        (select Candidate_ID, max(Entry_Date) as maxEntry_date from dbo.Candidate_Activity
                  where Act_Type = 'LT App Sig'
                  group by Candidate_ID) CA ON C.Candidate_Id = CA.Candidate_Id
ORDER BY CA.maxEntry_Date DESC, C.Last_Name, C.First_Name
0
 
hnasrCommented:
If you have table a:
id    date
1     date1- recent
1     date2
1     date3
2     date4
2     date5
2     date6 - recent

Result required include:
id    date
1     date1- recent
2     date6 - recent

Compare with this:

use AdventureWorks2008R2 ;
go
select  h.OrganizationLevel, (Select MAX(HireDate) from HumanResources.Employee as m where m.OrganizationLevel=h.OrganizationLevel) as maxHireDate
from HumanResources.Employee h
where h.HireDate =(Select MAX(HireDate) as maxHired from HumanResources.Employee as m where m.OrganizationLevel=h.OrganizationLevel)
group by h.OrganizationLevel;

Result:
OrganizationLevel      maxHireDate
0                            2003-02-15
1                            2005-03-18
2                            2007-04-15
3                            2007-07-01
4                            2005-01-25
0
 
ralmadaCommented:
Although by looking at your query again, you could just simply do:

SELECT  max(CA.Entry_Date AS Date) as EntryDate, 
	C.Candidate_Id, 
	C.First_Name, 
	C.Last_Name, 
        C.Phone_1 AS Phone, 
	C.Email_Address AS Email
FROM          dbo.Candidates C 
INNER JOIN dbo.Candidate_Activity CA ON C.Candidate_Id = CA.Candidate_Id
WHERE      (CA.Act_Type = 'LT App Sig')
group by C.Candidate_Id, 
	C.First_Name, 
	C.Last_Name, 
        C.Phone_1 AS Phone, 
	C.Email_Address AS Email
ORDER BY EntryDate DESC, C.Last_Name, C.First_Name

Open in new window

0
 
fabi2004CIOAuthor Commented:
I get ADO error:  max is not a recognized function name
0
 
ralmadaCommented:
1) What query are you trying? Can you post the exact query producing the error?
2) Can you please clarify what database engine are you using?
0
 
ralmadaCommented:
Also, if you are running this on Excel, drop the dbo identifiers there:

SELECT  max(CA.Entry_Date AS Date) as EntryDate, 
	C.Candidate_Id, 
	C.First_Name, 
	C.Last_Name, 
        C.Phone_1 AS Phone, 
	C.Email_Address AS Email
FROM          Candidates C 
INNER JOIN Candidate_Activity CA ON C.Candidate_Id = CA.Candidate_Id
WHERE      (CA.Act_Type = 'LT App Sig')
group by C.Candidate_Id, 
	C.First_Name, 
	C.Last_Name, 
        C.Phone_1 AS Phone, 
	C.Email_Address AS Email
ORDER BY EntryDate DESC, C.Last_Name, C.First_Name

Open in new window

0
 
fabi2004CIOAuthor Commented:
I'm not using Excel.  I am using Access 2010 to open an .adp file with the backend db being on an SQL 2K server.

I used the query you posted above:

SELECT  max(CA.Entry_Date AS Date) as EntryDate,
      C.Candidate_Id,
      C.First_Name,
      C.Last_Name,
        C.Phone_1 AS Phone,
      C.Email_Address AS Email
FROM          dbo.Candidates C
INNER JOIN dbo.Candidate_Activity CA ON C.Candidate_Id = CA.Candidate_Id
WHERE      (CA.Act_Type = 'LT App Sig')
group by C.Candidate_Id,
      C.First_Name,
      C.Last_Name,
        C.Phone_1 AS Phone,
      C.Email_Address AS Email
ORDER BY EntryDate DESC, C.Last_Name, C.First_Name
0
 
fabi2004CIOAuthor Commented:
I also tried it in Query Analyzer and got a similar error:

'max' is not a recognized function name.
0
 
ralmadaCommented:
Sorry I meant to say Access, not Excel there. Anyhow, can you please clarify how are you using this query? It should work fine in SQL 2000, so the only choice is that the way you're running it might have some issues
0
 
fabi2004CIOAuthor Commented:
I copied and pasted your code into SQL 2K Query Analyzer.  I did not change anything other than to choose the correct database from the dropdown menu in query analyzer.

I had also tried it in Access previously.
0
 
fabi2004CIOAuthor Commented:
I saw that just as you were typing it out.  Also had to remove the AS Phone and AS Email from the GROUP BY clause.

It works beautifully now!

Thank you very much.

SELECT      MAX(CA.Entry_Date) AS Date, C.Candidate_Id, C.First_Name, C.Last_Name, C.Phone_1 AS Phone, C.Email_Address AS Email
FROM          dbo.Candidates C INNER JOIN
                        dbo.Candidate_Activity CA ON C.Candidate_Id = CA.Candidate_Id
WHERE      (CA.Act_Type = 'LT App Sig')
GROUP BY C.Candidate_Id, C.First_Name, C.Last_Name, C.Phone_1, C.Email_Address
ORDER BY Date DESC, C.Last_Name, C.First_Name
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.