I have a table called Data that I want to grab the row for the 'id' that is most recent to my specified 'DateTime'. For example the following table I want the specific row for each 'id' that is most recent to my 'DateTime' of 2022-03-04 12:00:00.

 ---- --------- --------------------------- 
| id | Value   | DateTime                  |
 ---- --------- --------------------------- 
|  1 | 495     | 2022-03-02 11:03:15.353   |
|  1 | xyz123  | 2022-03-03 12:03:15.353   |
|  2 | yxz3474 | 2022-03-03 12:03:15.353   |
|  2 | 345345  | 2022-03-03 10:33:15.353   |
|  2 | mdfn54  | 2022-03-03 12:09:35.445   |
|  3 | puip5   | 2022-03-01 12:03:15.353   |
 ---- --------- --------------------------- 

So what should be returned is the following:

| 1 | xyz123 | 2022-03-03 12:03:15.353 |

| 2 | mdfn54 | 2022-03-03 12:09:35.445 |

| 3 | puip5 | 2022-03-01 12:03:15.353 |

CodePudding user response：

1. calculate the diff between datetime and specified time
2. pick the smallest one

with cte as (
select id,
       value,
       datetime,
       row_number() over (partition by id order by abs(timestampdiff(microsecond,datetime,'2022-03-04 12:00:00'))) as rn
  from data)
select id,
       value,
       datetime
  from cte
 where rn = 1;
``