Get minimum value greater than zero

sql-server, t-sql

Solution

I would recommend using nullif() so your query would be

SELECT id_function = @param,
   MIN(t1.column1) AS c1min, 
   MAX(t1.column2) AS c2max,
   MIN(NULLIF(t1.column3,0) AS c3min
FROM table1 (NOLOCK) AS t1
WHERE t1.id = @param

that way you don't risk altering your results, e.g. if your real minimum in column 3 is 100 the previous answer would affect your results, and also if you only have zeros in your column 3 column the previous answer would also deliver incorrect results

Problem

I have the following table: ``` column1 column2 column3 3 2 0 5 9 2 1 4 6 ``` When I run the following code: ``` SELECT id_function = @param, MIN(t1.column1) AS c1min, MAX(t1.column2) AS c2max, MIN(t1.column3) AS c3min FROM table1 (NOLOCK) AS t1 WHERE t1.id = @param ``` I get: ``` c1min c2max c3min 1 9 0 ``` My problem is that c3min must be the minimum value greater than zero. The result I need should be: ``` c1min c2max c3min 1 9 2 ``` Is there any way to do that without using a subselect? Any help will be appreciated. Thank you!

Original source