Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to figure out which column/value the COALESCE operator successfully selected?

I have a table that I wish to find the first non-null value from 3 (and only 3) columns for each ID starting with Col1 then to Col2 then to Col3

Note: Col3 is NEVER NULL

ID    Col1    Col2    Col3
------------------------------
1     A       B       X
2     NULL    C       X
3     NULL    NULL    X
4     D       NULL    X

To get the correct column for each value I use the following SQL Select

SELECT    ID,
          COALESCE(Col1, Col2, Col3) AS Col
FROM      MyTable

which returns the following and works just fine

ID    Col
-------------
1     A
2     C
3     X
4     D

What I want is a third column returned indicating which column the coalesce was successful on. The following is the result set that I wish to produce:

ID    Col    Source
-----------------------
1     A      Col1
2     C      Col2
3     X      Col3
4     D      Col1
like image 845
Jon Erickson Avatar asked May 27 '09 18:05

Jon Erickson


People also ask

What is the output of select coalesce?

The COALESCE() function returns the first non-null value in a list.

How do you use coalesce for multiple columns?

The coalesce in MySQL can be used to return first not null value. If there are multiple columns, and all columns have NULL value then it returns NULL otherwise it will return first not null value. The syntax is as follows. SELECT COALESCE(yourColumnName1,yourColumnName2,yourColumnName3,.......

How do you coalesce a column in SQL?

SQL COALESCE and CASE expressionSELECT COALESCE (expression1, expression2, expression3) FROM TABLENAME; The above Coalesce SQL statement can be rewritten using the CASE statement. The query returns the same result as the one that uses the COALESCE function.

How do you use coalesce in where clause?

The COALESCE expression returns the first non-null expression. If all expressions evaluate to NULL, then the COALESCE expression return NULL; Because the COALESCE is an expression, you can use it in any clause that accepts an expression such as SELECT , WHERE , GROUP BY , and HAVING .


1 Answers

Perhaps this will work?

SELECT    ID,
          COALESCE(Col1, Col2, Col3) AS Col,
          CASE COALESCE(Col1, Col2, Col3)
              WHEN Col1 THEN 'Col1'
              WHEN Col2 THEN 'Col2'
              WHEN Col3 THEN 'Col3'
              ELSE 'Unknown'
          END AS Source
FROM      MyTable
like image 175
Lasse V. Karlsen Avatar answered Jan 03 '23 18:01

Lasse V. Karlsen