0

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.

Anthony The Nguyen
  • 25
  • 5
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) (just replace `1` with `4`) – Thom A Dec 09 '22 at 17:19
  • since there is no unified TestTime for each ID-set, is there a guaranteed timeframe between ID-set postings? In your example, ID 001 has a set on 9/12 and again on 9/14. Are they always days apart? Or can we guarantee that each ID will have at least 30 minutes between TestTimes? From your example, each ID-set happens over 14 minutes. Or maybe we can look only at Channel 1 postings for the ID, and then find the most recent TestTimes for each channel from there. Is there any guarantee that the ID 001 TestTimes won't overlap with another ID 001 set? – Tim Jarosz Dec 09 '22 at 17:24

1 Answers1

1

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
Patrick Hurst
  • 2,086
  • 1
  • 3
  • 13