CODEWITHSUNDEEP
sqlselect
Uzg
Uzg

Reputation: 319

SQL: Select the minimum value from multiple columns with null values

I have a table like this one 

ID   Col1   Col2   Col3
--   ----   ----   ----
1      7    NULL    12  
2      2     46    NULL
3     NULL  NULL   NULL
4     245     1    792

I wanted a query that yields the following result 

 ID   Col1   Col2   Col3  MIN
 --   ----   ----   ----  ---
  1     7    NULL    12    7
  2     2     46    NULL   2
  3    NULL  NULL   NULL  NULL
  4    245    1     792    1

I mean, I wanted a column containing the minimum values out of Col1, Col2, and Col 3 for each row ignoring NULL values. In a previous question (What's the best way to select the minimum value from multiple columns?) there is an answer for non NULL values. I need a query as efficient as possible for a huge table.

Select Id,
       Case When Col1 < Col2 And Col1 < Col3 Then Col1
            When Col2 < Col1 And Col2 < Col3 Then Col2 
            Else Col3
            End As MIN
From   YourTableNameHere

Upvotes: 5

Views: 19365

Answers (6)

practicalSQL
practicalSQL

Reputation: 33

I found this solution to be more efficient than using multiple case statement clauses, which can get extremely lengthy when evaluating data from several columns across one row.

Also, I can't take credit for this solution as I found it on some website a year or so ago. Today I needed a refresh on this logic, and I couldn't find it anywhere. I found my old code and decided to share it in this forum now.

Creating your test table:

create table #testTable(ID int, Col1 int, Col2 int, Col3 int)
Insert into #testTable values(1,7,null,12)
Insert into #testTable values(2,2,46,null)
Insert into #testTable values(3,null,null,null)
Insert into #testTable values(4,245,1,792)

Finding min value in row data:

Select ID, Col1, Col2, Col3 ,(SELECT Min(v) FROM (  VALUES  (Col1), (Col2), (Col3)  ) AS value(v)) [MIN] from #testTable order by ID

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Just modify your query with coalesce():

Select Id,
       (Case When Col1 <= coalesce(Col2, col3, col1) And
                  Col1 <= coalesce(Col3, col2, col1)
            Then Col1
            When Col2 <= coalesce(Col1, col3, col2) And
                 Col2 <= coalesce(Col3, col1, col2)
            Then Col2 
            Else Col3
        End) As MIN
From YourTableNameHere;

This doesn't require inventing a "magic" number or over-complicating the logic.

Upvotes: 0

FuzzyTree
FuzzyTree

Reputation: 32392

You didn't specify which version of Teradata you're using. If you're using version 14+ then you can use least.

Unfortunately least will return null if any of its arguments are null. From the docs:

LEAST supports 1-10 numeric values. If numeric_value is the data type of the first argument, the return data type is numeric. The remaining arguments in the input list must be the same or compatible types. If either input parameter is NULL, NULL is returned.

But you can get around that by using coalesce as Joe did in his answer.

select id, 
  least(coalesce(col1,9999),coalesce(col2,9999),coalesce(col3,9999))
from mytable

Upvotes: 3

void
void

Reputation: 7880

in this way you don't need to check for nulls, just use min and a subquery

select tbl.id,tbl.col1,tbl.col2,tbl.col3,
      (select min(t.col) 
      from ( 
            select col1 as col from tbl_name t where t.id=tbl.id
            union all
            select col2 as col from tbl_name t where t.id=tbl.id
            union all
            select col3 as col from tbl_name t where t.id=tbl.id 
           )t) 
from tbl_name tbl

Output:

1   7       NULL    12      7
2   2       46      NULL    2
3   NULL    NULL    NULL    NULL
4   245     1       792     1

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135799

Assuming you can define some "max" value (I'll use 9999 here) that your real values will never exceed:

Select Id,
       Case When Col1 < COALESCE(Col2, 9999)
             And Col1 < COALESCE(Col3, 9999) Then Col1
            When Col2 < COALESCE(Col1, 9999) 
             And Col2 < COALESCE(Col3, 9999) Then Col2 
            Else Col3
       End As MIN
    From YourTableNameHere;

Upvotes: 6

Aiken
Aiken

Reputation: 272

This might work:

Select id, Col1, Col2, Col3, least(Col1, Col2, Col3) as MIN From YourTableNameHere

Upvotes: 0

Related Questions