SQL Return the 4 most recent entries for each ID (non-unique)-CodePudding

Each ID has at least a single set of 4 entries, one for each channel 1-4, and can be repeated with different dates.
So ID 001 may have 8 entries, 2 sets, and ID 002 may just have 4 entries, 1 set.
I am wondering if it is possible to return the most recent 4 entries for each ID.

Here is my database setup
ID (not unique)
TestTime (datetime)
Channel (Values only 1,2,3 or 4)
Data

Example:

ID         TestTime        Channel Data
001   2022-09-14 16:42:00     1    2.5
001   2022-09-14 16:46:00     2    3.5
001   2022-09-14 16:50:00     3    1.5
001   2022-09-14 16:56:00     4    4.5

002   2022-09-13 16:42:00     1    0.5
002   2022-09-13 16:46:00     2    2.5
002   2022-09-13 16:50:00     3    1.5
002   2022-09-13 16:56:00     4    3.5

001   2022-09-12 16:42:00     1    1.5
001   2022-09-12 16:46:00     2    0.5
001   2022-09-12 16:50:00     3    1.5
001   2022-09-12 16:56:00     4    2.5

Goal Output:

ID         TestTime        Channel Data

002   2022-09-13 16:42:00     1    0.5
002   2022-09-13 16:46:00     2    2.5
002   2022-09-13 16:50:00     3    1.5
002   2022-09-13 16:56:00     4    3.5

001   2022-09-14 16:42:00     1    2.5
001   2022-09-14 16:46:00     2    3.5
001   2022-09-14 16:50:00     3    1.5
001   2022-09-14 16:56:00     4    4.5

Can be ordered by TestTime Ascending or Descending.

CodePudding user response：

You can use a sub query on your dataset to isolate the four most recent dates per ID:

DECLARE @Table TABLE (ID NVARCHAR(3), TestTime DATETIME, Channel TINYINT, Data DECIMAL(4,2))
INSERT INTO @Table (ID, TestTime, Channel, Data) VALUES 
('001', '2022-09-14 16:42:00', 1, 2.5),
('001', '2022-09-14 16:46:00', 2, 3.5),
('001', '2022-09-14 16:50:00', 3, 1.5),
('001', '2022-09-14 16:56:00', 4, 4.5),

('002', '2022-09-13 16:42:00', 1, 0.5),
('002', '2022-09-13 16:46:00', 2, 2.5),
('002', '2022-09-13 16:50:00', 3, 1.5),
('002', '2022-09-13 16:56:00', 4, 3.5),

('001', '2022-09-12 16:42:00', 1, 1.5),
('001', '2022-09-12 16:46:00', 2, 0.5),
('001', '2022-09-12 16:50:00', 3, 1.5),
('001', '2022-09-12 16:56:00', 4, 2.5)

SELECT t.*
  FROM @Table t
    INNER JOIN (SELECT ID, TestTime, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TestTime DESC) AS rn FROM @Table) a
      ON t.ID = a.ID
      AND t.TestTime = a.TestTime
      AND rn <= 4

ID  TestTime                Channel Data
----------------------------------------
001 2022-09-14 16:42:00.000 1       2.50
001 2022-09-14 16:46:00.000 2       3.50
001 2022-09-14 16:50:00.000 3       1.50
001 2022-09-14 16:56:00.000 4       4.50
002 2022-09-13 16:42:00.000 1       0.50
002 2022-09-13 16:46:00.000 2       2.50
002 2022-09-13 16:50:00.000 3       1.50
002 2022-09-13 16:56:00.000 4       3.50