How to get the minimum value in a set of columns while excluding a certain value?

Question

I have been trying a query to select the minimum value in a row but also exclude a certain value (-998).

The table looks like this:

col1        col2         col3
----------------------------------
1            1          -998
2           -998        2
3            2          1
-998         1          3

So in the first row, the minimum value would be 1; in the second row, it would be 2; and in the third row, it would be 1 again.

I tried using a case statement and excluding -998 in each condition, but it keeps grabbing -998 for some reason.

SELECT  
    CASE
        WHERE (col1 &lt;= col2 and col1 &lt;= col3) and col1 != -998 THEN col1
        WHERE (col2 &lt;= col1 and col2 &lt;= col3) and col2 != -998 THEN col2
        WHERE (col3 &lt;= col1 and col3 &lt;= col2) and col3 != -998 THEN col3
    END AS [MIN_VAL]
FROM myTable

If anyone can point me in the right direction that would ge awesome.

possible duplicate of [Get MAX and MIN values of each rows in sql server](http://stackoverflow.com/questions/16166225/get-max-and-min-values-of-each-rows-in-sql-server) — Mudassir Hasan, Nov 18 '14 at 06:20
I did not understand how the minimum value in col2 is '2'. It should be 1 right? Can u pls explain! — Sudhir kumar, Nov 18 '14 at 06:20
Since we are excluding -998 the only values in row 2 are 2 and 2 so it should be 2 — user1655006, Nov 18 '14 at 06:23

score 5 · Answer 1 · answered Nov 18 '14 at 06:26

Use the table value constructor to unpivot your column values and exclude values from there.

SQL Fiddle

MS SQL Server 2012 Schema Setup:

create table YourTable
(
  col1 int,
  col2 int,
  col3 int
);


insert into YourTable values
(1    ,        1   ,       -998),
(2    ,       -998 ,       2   ),
(3    ,        2   ,       1   ),
(-998 ,        1   ,       3   );

Query 1:

select (
       select min(R.Value)
       from (values(T.col1),
                   (T.col2), 
                   (T.col3)) as R(Value)
       where R.Value <> -998
       ) as min_val
from YourTable as T;

Results:

| MIN_VAL |
|---------|
|       1 |
|       2 |
|       1 |
|       1 |

Nice! I've never tried using table value constructor. Thanks for this — Felix Pamittan, Nov 18 '14 at 06:36

score 0 · Answer 2 · answered Nov 18 '14 at 06:24

How about this:

use tempdb

create table myTable(
    col1 int,
    col2 int,
    col3 int
)
insert into myTable values
(1, 1, -998),
(2, -998, 2),
(3, 2, 1),
(-998, 1, 3)

;with cte as(
    select
        rn = row_number() over(order by (select null)),
        col = col1
    from myTable
    union all
    select
        rn = row_number() over(order by (select null)),
        col = col2
    from myTable
    union all
    select
        rn = row_number() over(order by (select null)),
        col = col3
    from myTable
)
select
    minimum = min(col) 
from cte
where col <> - 998
group by rn

drop table mytable

score 0 · Answer 3 · answered Nov 18 '14 at 06:25

SELECT
     CASE
         WHEN (col1 <= col2 or col2 = -998)
              and (col1 <= col3 or col3 = -998)
              and col1 != -998 
         THEN col1
         WHEN (col2 <= col1 or col1 = -998)
              and (col2 <= col3 or col3 = -998)
              and col2 != -998 
         THEN col2
         WHEN (col3 <= col1 or col1 = -998)
              and (col3 <= col2 or col2 = -998)
              and col3 != -998 
         THEN col3
     END AS [MIN_VAL]
FROM myTable;

How to get the minimum value in a set of columns while excluding a certain value?

3 Answers3