SQL: select min to subtract two columns, and also select the numbers used to subtract min

Question

I have two columns, I want to select: Price, min abs. the difference between Price and Strike, and Strike corresponds to min abs. difference calculation. I'm new to SQL, so forgive me if this is easy.

Price   Strike
30.8    29
30.8    30
30.8    31
30.2    29
30.2    30
30.2    31

The answer I want to get is:

Price  Diff  Strike
30.8   .2    31
30.2   .2    30

The code I have so far is:

Select min(price) as 'Price',
min(abs(price - Strike)) as 'Diff'
from Table
group by price

I do not know how to choose the right blow.

Thank you

Answer 1

I will use Window Functionfor this.

SELECT Price,
       Diff,
       Strike
FROM   (SELECT *,
               Abs(price - Strike) Diff,
               Row_number()OVER(partition BY price ORDER BY Abs(price - Strike) )rn
        FROM   Yourtable) a
WHERE  rn = 1 

, price Abs(price - Strike) , , min, Dense_Rank

SELECT Price,
       Diff,
       Strike
FROM   (SELECT *,
               Abs(price - Strike) Diff,
               Dense_Rank()OVER(partition BY price ORDER BY Abs(price - Strike) )rn
        FROM   Yourtable) a
WHERE  rn = 1 

Answer 2

- ( , RANK()):

SELECT price, strike, difference
  FROM (
    SELECT price, strike, ABS(price - strike) AS difference
         , RANK() OVER ( ORDER BY ABS(price - strike) ) AS rn
      FROM mytable
) x
 WHERE x.rn = 1;

DENSE_RANK() ( ).