CODEWITHSUNDEEP
sql-server
Mohammad Niagara
Mohammad Niagara

Reputation: 11

SQL Selecting All columns based on max value of two column

I have a table like this

PkReferencesID    FkReferencesID   DocID    FKHRSEmployeeID  AssignedDate  AssignedTime  FKActivityID
40855             NULL             8530     13               13920625      0829          1
40856             40855            8530     438              13920625      0829          1
40857             40856            8530     13               13920625      0829          2
40858             40857            8530     438              13920625      0830          1
40859             40858            8530     NULL             13920625      0830          1
NULL              NULL             NULL     NULL             NULL          NULL          NULL

How i can select rows with DocID = 8530 and FKActivityID = 1 and Maximum AssignedDate and Maximum AssignedTime for per FKHRSEmployeeID?

Note:the result should be one row for per FKHRSEmployeeID

Any help would be appriciated. Thanks

The result must be like this

PkReferencesID FkReferencesID DocID FKHRSEmployeeID AssignedDate AssignedTime FKActivityID

40855 NULL 8530 13 13920625 0829 1 40858 40857 8530 438 13920625 0830 1

Upvotes: 1

Views: 1095

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

The easiest way to do this is with row_number():

select t.*
from (select t.*,
             row_number() over (partition by FKHRSEmployeeID
                                order by AssignedDate desc, AssignedTime desc
                               ) as seqnum
      from t
      where DocID = 8530 and FKActivityID = 1 
     ) t
where seqnum = 1

Upvotes: 2

Clon
Clon

Reputation: 1055

Query 2 will retrieve the information you're asking for:

Query1: This query gets the maximum assigned date/time for each employee.

SELECT FKHRSEmployeeID, MAX(AssignedDate & AssignedTime)
FROM Table
WHERE T.DocID=8530 
  AND T.FKActivityID=1
GROUP BY FKHRSEmployeeID;

Query2: This query uses the former one to retrieve only the matching records.

SELECT T.*
FROM Table AS T 
  INNER JOIN Query1 AS Q 
    ON (T.Date=LEFT(Q.MaxDateTime,8)) 
    AND (T.Time=RIGHT(Q.MaxDateTime,4)) 
    AND (T.FKHRSEmployeeID=Q.FKHRSEmployeeID)
WHERE T.DocID=8530 
  AND T.FKActivityID=1;

This query by itself doesn't guarantee you'll only get one record per employee, unless the combination FKHRSEmployeeID/AssignedDate/AssignedTime is unique in the table. You can ensure this by creating a unique index based on those columns.

Upvotes: 0

Related Questions