How can I get the minimum sum value of fields without repeating where clause?

Question

How can I get the minimum value after adding value of 2 fields without repeating the where clause?

Example: Get records with minimum value of num1 + num2 and deleted_at is null

id          num1        num2       deleted_at
----------  ---------   ---------  ---------  
1           1           1          2015-01-01
2           2           2          null
3           2           2          null         
4           4           4          null 

Expected result is

id          num1        num2       deleted_at
----------  ---------   ---------  ---------
2           2           2          null   
3           2           2          null

I tried subquery, but I have to repeat the where clause

SELECT * 
FROM   foos t1 
WHERE  t1.num1 + t1.num2 = (SELECT Min(t2.num1 + t2.num2) 
                            FROM   foos t2 
                            WHERE  t2.deleted_at IS NULL) //repeat
       AND t1.deleted_at IS NULL //repeat

Answer 1

In Sql Server , you can do it with RANK

CREATE TABLE foos
    ([id] int, [num1] int, [num2] int, [deleted_at] datetime)
;

INSERT INTO foos
    ([id], [num1], [num2], [deleted_at])
VALUES
    (1, 1, 1, '2015-01-01'),
    (2, 2, 2, NULL),
    (3, 2, 2, NULL),
    (4, 4, 4, NULL)
;

--the select

SELECT [id], [num1], [num2]
FROM 
(
 SELECT [id], [num1], [num2]
  , RANK() OVER(ORDER BY [num1]+ [num2] ASC) as rk
 FROM foos
 WHERE [deleted_at] IS NULL
)as r
WHERE r.rk = 1 

output:

id  num1    num2
2      2    2
3      2    2

dbfiddle here

Answer 2

Why you don't want to repeat where clause ?

Are you facing any performance issue becasue of repeat where clause ?

you have to store the filtered result somewhere if you don't want to repeat.

For example CTE or table variable or temp table.

In my example I am using CTE.

CREATE TABLE #foos
    ([id] int, [num1] int, [num2] int, [deleted_at] datetime)
;

INSERT INTO #foos
    ([id], [num1], [num2], [deleted_at])
VALUES
    (1, 1, 1, '2015-01-01'),
    (2, 2, 2, NULL),
    (3, 2, 2, NULL),
    (4, 4, 4, NULL)

;

WITH CTE
AS (
    SELECT *
    FROM #foos
    WHERE [deleted_at] IS NULL
    )
    ,CTE1
AS (
    SELECT min(num1 + num2) minnum
    FROM CTE
    )
SELECT *
FROM cte f
WHERE EXISTS (
        SELECT minnum
        FROM cte1 c
        WHERE c.minnum = f.num1 + f.num2
        )


drop table #foos

Answer 3

You can use min() as a window function:

select id, num1, num2, num_sum
from (
  select id, 
         num1, 
         num2, 
         num1 + num2 as num_sum, 
         min(num1 + num2) over () as min_sum
  from foos
  where deleted_at is null
) t
where num_sum = min_sum;

The above is ANSI standard SQL and works well with Postgres.

Online example: http://rextester.com/NJWX76223