SQL query to find minimum difference between row values of a column

Question

I am trying to find the minimum difference between years for each ID. For example,

I have this data:

ID     year
1      2001
1      2001
2      2003
2      2004
2      2010
3      2000

and I want this output:

ID    year
1      0
2      1
3      0

Answer 1

You could use a LEFT JOIN

SELECT Id,
       ISNULL(MIN(Def), 0) [Year]
FROM
(
  SELECT T.Id,
         TT.Year - T.Year Def
  FROM Data T
  LEFT JOIN Data TT ON T.Id = TT.Id AND T.Year < TT.Year
) T
GROUP BY Id;

Online Demo