sql - Get record with min time difference -

sql - Get record with min time difference -

i have requirement need record min time difference current record. allow assume in table have insert date, grouping id , id column. have selected record , not want record difference between insert date of selected record , record min.

i have tried outer apply, query takes forever run.

query:

select e.id b.emp t id = 5 outer apply ( select top 1 * b.emp t.group_id = group_id order insert_time desc ) e

if indeed want find closest insert time selected record' group, regardless of direction, might help:

select t.insert_date, ca.* b.emp t outer apply ( select top (1) * b.emp e e.group_id = t.group_id , e.id != t.id order abs(datediff(ms, t.insert_date, e.insert_date)) ) ca t.id = 5;

edit: need check indexing options, though. starters, assuming have clustered primary key on id column, should help:

create index [ix_b_emp_groupinsert] on b.emp (group_id, insert_time);

sql sql-server tsql