1

Here is my SQL Server table:

 DECLARE @EMP TABLE
    (
        Id INT,
        NAME VARCHAR(200),
        AlarmOnTimeStamp DATETIMEOFFSET,
        AlarmOffTimeStamp DATETIMEOFFSET NULL
    );

DECLARE @EMPCOMMENT TABLE
(
    EmpId INT,
    Comment VARCHAR(2000)
)

INSERT INTO @EMP VALUES (1121, 'Test1', '2020-04-09 01:56:29.507', NULL)
INSERT INTO @EMP VALUES (57, 'Test1', '2020-04-09 02:56:29.507', NULL)
INSERT INTO @EMP VALUES (992, 'Test2', '2020-04-09 01:56:29.507', '2020-04-09 03:56:29.507')

INSERT INTO @EMPCOMMENT VALUES (2, 'Test1')
INSERT INTO @EMPCOMMENT VALUES (2, 'Test2')

SELECT *
FROM @emp e
LEFT JOIN @EMPCOMMENT ec ON ec.Id = e.Id

SELECT
    *,
    rn = ROW_NUMBER() OVER (PARTITION BY e.Name ORDER BY AlarmOnTimeStamp, e.Name DESC) 
FROM @emp e
LEFT JOIN @EMPCOMMENT ec ON e.Id = ec.EmpId

enter image description here

Output:

  Id      Name      AlarmOnTimeStamp           AlarmOffTimeStamp      EmpId     Comment    rn
  --------------------------------------------------------------------------------------------
  2       Test1      2020-04-09 02:56:29         NULL                  2          Good      1
  2       Test1      2020-04-09 02:56:29         NULL                  2          Best      1
  3       Test2      2020-04-09 01:56:29  2020-04-09 03:56:29.50       NULL       NULL      1

I just want the above result

If you see in the screenshot the 1st record show be removed, reason as it an old record, I just wanted if employee name are same, get its latest record with all its comment

Dale K
  • 25,246
  • 15
  • 42
  • 71
t-bolt
  • 21
  • 1
  • 5

1 Answers1

0

I think that you want:

SELECT *
FROM (
    SELECT *, RANK() OVER(PARTITION BY e.Name ORDER BY e.AlarmOnTimeStamp desc) rn
    FROM @emp e
    LEFT JOIN @EMPCOMMENT ec ON ec.EmpId = e.Id
) t
WHERE rn = 1

This ranks rows having the same employee name by descending id, and keep the top rows only (ties included).

For your sample data, the query yields:

Id | NAME  | AlarmOnTimeStamp            | AlarmOffTimeStamp           | EmpId | Comment | rn
-: | :---- | :-------------------------- | :-------------------------- | ----: | :------ | :-
 2 | Test1 | 2020-04-09 02:56:29.5070000 | null                        |     2 | Test1   | 1 
 2 | Test1 | 2020-04-09 02:56:29.5070000 | null                        |     2 | Test2   | 1 
 3 | Test2 | 2020-04-09 01:56:29.5070000 | 2020-04-09 03:56:29.5070000 |  null | null    | 1
GMB
  • 216,147
  • 25
  • 84
  • 135
  • thanks, but is there any better way to do instead of taking order by e.id, cause i have just given an example this Id column can be uniqueidentifier, that's why i have to depend on Name and AlarmOnTimeStamp column – t-bolt Apr 13 '20 at 17:39
  • @t-bolt: sure, you can use `AlarmOnTimeStamp` for ordering. I adapted the query. – GMB Apr 13 '20 at 17:58