• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 526
  • Last Modified:

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
0
fabi2004
Asked:
fabi2004
  • 8
  • 6
1 Solution
 
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
 
fabi2004Author 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
fabi2004Author 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
 
fabi2004Author 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
 
fabi2004Author 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
 
fabi2004Author 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
 
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
 
fabi2004Author 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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now