CODEWITHSUNDEEP
sqlsql-server
user1655006
user1655006

Reputation: 33

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

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 <= col2 and col1 <= col3) and col1 != -998 THEN col1
        WHERE (col2 <= col1 and col2 <= col3) and col2 != -998 THEN col2
        WHERE (col3 <= col1 and col3 <= 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.

Upvotes: 1

Views: 180

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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 |

Upvotes: 5

Multisync
Multisync

Reputation: 8797

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;

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

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

Upvotes: 0

Related Questions