Get minimum value greater than zero

Question

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!

Solution 1

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

Solution 2

You could use a case to set the 0 value to a higher value on your min() condition

SELECT id_function = @param,
       MIN(t1.column1) AS c1min, 
       MAX(t1.column2) AS c2max,
       MIN(case when t1.column3 = 0 then 99 else t1.column3 end) AS c3min
FROM table1 (NOLOCK) AS t1
WHERE t1.id = @param

Solution 3

It work .

(But I thing answer of :Hedinn is best answer ).

SELECT  id_function = @param ,
    c1min = ( SELECT    MIN(t1Sub.column1)
              FROM      table1 (NOLOCK) AS t1Sub
              WHERE     t1Sub.id = @param
            ) ,
    c2max = ( SELECT    MAX(t2Sub.column2)
              FROM      table1 (NOLOCK) AS t2Sub
              WHERE     t2Sub.id = @param
            ) ,
    c3min = ( SELECT    MIN(t3Sub.column3)
              FROM      table1 (NOLOCK) AS t3Sub
              WHERE     ( t3Sub.id = @param )
                        AND ( t3Sub.column3 <> 0 )
            )
FROM    table1 (NOLOCK) AS t1
WHERE   ( t1.id = @param )

Comments

[Hedinn]

if your real minimum in column 3 is 100 this answer would affect your results, and also if you only have zeros in your column 3 column this would also deliver incorrect results

[juergen d]

Yes, you need to pick values bigger than the possible lowest of course.

[Hedinn]

but that doesn't solve the problem if there are only zeros in the column

[stefanobaldo]

I changed to ISNULL(MIN(NULLIF(t1.column3,0), 0) AS c3min because if I have only zeros it would return me NULL as the min value. It's working perfectly.

[stefanobaldo]

It works, but I asked if it can be done without using subselects. Thank you anyway! :)

[pvdjay]

Ah! NULLIF()... Very useful!