我想要查询一个表的上一次记录 左连接条件取时间差最小值 这个怎么写

我想要查询一个表的上一次记录 左连接条件取时间差最小值 这个怎么写？
例如 表1
id NO outkey inkey date
1 1001 101 2022-10-19 08:10:54
2 1001 102 101 2022-10-19 09:10:54
3 1001 103 102 2022-10-19 10:10:54
4 1001 101 103 2022-10-19 14:10:54

我想要的是 select *from 表1 a left join 表1 b on a.no=b.no and a.inkey=b.outkey and 两个时间的最小值
这个该怎么写

问题不是很清楚，关联之后根据时间排序不行么？

1、直接通过left join是不行的，还需要加工一下才行，SQL如下：

WITH t0 AS (
        SELECT 1 id, 1001 AS no, 101 outkey, NULL inkey, '2022-10-19 08:10:54' date UNION ALL
        SELECT 2, 1001, 102, 101, '2022-10-19 09:10:54' UNION ALL
        SELECT 3, 1001, 103, 102, '2022-10-19 10:10:54' UNION ALL
        SELECT 4, 1001, 101, 103, '2022-10-19 14:10:54' )
    , t1 AS (
        SELECT a.*, b.id b_id, b.no b_no, b.outkey b_out_key, b.inkey b_inkey, b.date b_date
             , ROW_NUMBER( ) OVER( PARTITION BY a.id ORDER BY abs( datediff( ss, a.date, b.date ) ) ) rn
          FROM t0 a LEFT JOIN t0 b ON a.NO = b.NO AND a.inkey = b.outkey )
SELECT id, no, outkey, inkey, date, b_id, b_no, b_out_key, b_inkey, b_date FROM t1 WHERE rn = 1;