SQL - Getting most recent date from rows

I have a complex query that I'm hoping to get smoe help on.

I have 2 Tables, linked together by IDs.  (agentID and resourceID)
One table contains a users information (ex: First Name, Last Name, Group Name, extension)
The second table contains a log of actions and a datetime field containing it's time of occurrance.

The problem that I am having is that 1 User, ex: John Doe, could actually have 7 different IDs, in Table 1, and several action logs linked to those ID in Table2 based on what Group he is in. (each group a user is in creates a new ID for them)

My query needs to ONLY pull the most recent datetime row for each users First and Last Name, regardless of what ID it is coming from. You will see that there is an extension that is common for each user which could be used to group each one together with all of their other records.

Here's an example of data:

Table1
resourceID  | FirstName  | LastName  | GroupName | extension |
10 | John | Doe |1  | 2002 |
11 | John | Doe | 2  | 2002 |
12 | John | Doe | 3  | 2002|
13 | Jane | Doe | 1  | 2005 |
14 | John | Smith | 1 |  2008 |
15 | John | Smith | 3 | 2008 |

Table 2
agentID    |   DateTime   | Status   | Event   |
10  |   2009-07-07 09:11:56   |   1   |   20   |
10  |   2009-07-05 06:12:45   |   1   |   30   |
11  |   2009-07-05 06:11:35   |   1   |   30   |
11  |   2009-07-05 06:12:55   |   1   |   10   |
12  |   2009-07-05 06:12:59   |   1   |   30   |
12  |   2009-07-07 06:04:23   |   2   |   10   |
12  |   2009-07-07 09:07:33   |   1   |   20   |
13  |   2009-07-06 09:11:14   |   1   |   20   |
14  |   2009-07-07 09:17:26   |   3   |   50   |


Results would be
John Doe | 2009-07-05 06:12:45   |   1   |   30   |
Jane Doe | 2009-07-06 09:11:14   |   1   |   20   |
John Smith | 2009-07-07 09:17:26   |   3   |   50   |

Any help would be great!
MrBaseball9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please clarify if you are ussing mysql or mssql, and what version

note: this design is not really good (aka not normalized)

apart from that
select t1.firstname, t1.lastname, max(t2.datetime)
  from table1 t1
  left join table2 t2
    on t2.agentid = t1.resorceid
group by t1.firstname, t1.lastname

Open in new window

0
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT FirstName+' '+ LastName, MaxDate , status, Event
FROM Table1 a
INNER JOIN  (SELECT AgentID, MAX(Datetime) maxDate FROM Table2 GROUP BY AgentID) b
INNER JOIN Table2 c on c.agentID = b.agentID and c.datetime = b.MaxDate
0
MrBaseball9Author Commented:
MSSQL and my query is going into  sqlsrv_query
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.

MrBaseball9Author Commented:
PHP 5.2.10
0
MrBaseball9Author Commented:
NOTE: I need to collect all of the data from the row that contains the most recent DATE, per user.
0
profyaCommented:

SELECT FirstName + ' ' + LastName, b.[DateTime], b.Status, b.Event  FROM Tabl1 a INNER JOIN (SELECT AgentID, Max([DateTime]) As [DateTime], Status, Event FROM Table 2 GROUP BY AgentID, Status, Event) b ON a.AgentId=b.AgentId

Open in new window

0
MrBaseball9Author Commented:
No Dice so far on the queries.

A persons name "John Doe" has multiple entries in both tables. I need to pull out a row from Table2 that contains the most recent date for each user.

Below is the query that gets the most recent date and info based on agentID.
This is close, but since "John Doe" has several IDs, he's getting displayed several times.

SELECT p.resourceFirstName, p.resourceLastName, p.assignedTeamID, e.eventDateTime, e.eventType, e.reasonCode
FROM Resource p 
LEFT OUTER JOIN
AgentStateDetail e INNER JOIN
(SELECT agentID, MAX(eventDateTime) AS max_datetime
FROM AgentStateDetail
GROUP BY agentID) eMax ON eMax.agentID = e.agentID AND eMax.max_datetime = e.eventDateTime ON p.resourceID = e.agentID
WHERE     (e.agentID IS NOT NULL)
ORDER BY p.assignedTeamID, p.resourceLastName, p.resourceFirstName ASC

Open in new window

0
Anthony PerkinsCommented:
SELECT  r.resourceFirstName,
        r.resourceLastName,
        r.assignedTeamID,
        a.eventDateTime,
        a.eventType,
        a.reasonCode
FROM    Resource r
        INNER JOIN AgentStateDetail a ON r.resourceID = e.agentID
        INNER JOIN ( SELECT p.resourceFirstName,
                            p.resourceLastName,
                            MAX(e.eventDateTime) MaxeventDateTime
                     FROM   Resource p
                            INNER JOIN AgentStateDetail e ON p.resourceID = e.agentID
                     GROUP BY p.resourceFirstName,
                            p.resourceLastName
                   ) d ON r.resourceFirstName = d.resourceFirstName
                          AND r.resourceLastName = d.resourceLastName
                          AND a.eventDateTime = d.MaxeventDateTime
ORDER BY r.assignedTeamID,
        r.resourceLastName,
        r.resourceFirstName ASC
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MrBaseball9Author Commented:
ac,

This seems to be close, but when I run it through SQL Query Analyzer, I get the following message:

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'e' does not match with a table name or alias name used in the query.
0
MrBaseball9Author Commented:
I just changed e.agentID to a.agentID and it worked like a charm as far as I can see. I will try and test teh query a little more but I think it's working!
0
Anthony PerkinsCommented:
Yes, you are right.  This line:
INNER JOIN AgentStateDetail a ON r.resourceID = e.agentID

Should be:
INNER JOIN AgentStateDetail a ON r.resourceID = a.agentID
0
MrBaseball9Author Commented:
Thanks again!. I get lost on all of the inner joins!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.