CODEWITHSUNDEEP
sqlsql-server
Joshua Hateley
Joshua Hateley

Reputation: 13

SQL Server : Inner Join using bottom most row

I have a table referred to as 'attLog' which has the empID along with the time that they clocked out of work and also serialNo that counts up by one. I innerjoin this with a table know as 'employees' but when doing so it gives me the result closest to the top, but I need the most recent or most bottom result.

dbo.attLog

empID | time   | DATE      | serialNo
------+--------+-----------+----------
1001  | 5.01PM | 2020-10-1 | 1
1002  | 5.04PM | 2020-10-1 | 2
1002  | 5.02PM | 2020-10-2 | 3
1001  | 5.04PM | 2020-10-2 | 4
1002  | 5.15PM | 2020-10-2 | 5

This is the result that I get when I run the query..

dbo.employees

empID | latestTime
------+-----------
1001  | 5.04PM
1002  | 5.02PM

This is the result that I wish to happen..

dbo.employees

empID | latestTime
------+-----------
1001  | 5.04PM
1002  | 5.15PM

Here is my query that I am using..

UPDATE employees
SET employees.latestTime = attLog.time
FROM employees employees
INNER JOIN attLog attLog ON employees.empID = attLog.empID
WHERE attLog.authDate = '2020-10-2'

Thank you very much. Any help would be much appreciated.

Upvotes: 0

Views: 117

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Aggregate the table first and then join:

UPDATE e
    SET e.latestTime = al.max_time
    FROM employees e JOIN
         (SELECT al.empId, MAX(al.time) as max_time
          FROM attLog al
          WHERE al.authDate = '2020-10-02'
          GROUP BY al.empId
         ) al
         ON e.empID = al.empID;

Upvotes: 1

Related Questions