CODEWITHSUNDEEP
mysqlsqldatabase
Rabia Rana Khan
Rabia Rana Khan

Reputation: 202

How to select last column without Null in mySQL

I am facing an issue I cannot seem to find the solution of anywhere. I have a table in mySQL with following columns:

RoleDuty1
RoleDuty2
RoleDuty3
RoleDuty4
RoleDuty5
RoleDuty6
RoleDuty7

This table has just one row or record. I want to see which is the last column in the table which is Not NULL? meaning that I want to dynamically get Column Name RoleDuty7 as an answer from mySQL, since all of the columns have some value other than Null inside them.

Anyone has any idea how can I do this?

Upvotes: 3

Views: 185

Answers (3)

David Khuu
David Khuu

Reputation: 967

SELECT (
    CASE FirstNullColumn
        WHEN 'RoleDuty1' THEN 'None'
        WHEN 'RoleDuty2' THEN 'RoleDuty1'
        WHEN 'RoleDuty3' THEN 'RoleDuty2'
        WHEN 'RoleDuty4' THEN 'RoleDuty3'
        WHEN 'RoleDuty5' THEN 'RoleDuty4'
        WHEN 'RoleDuty6' THEN 'RoleDuty5'
        WHEN 'RoleDuty7' THEN 'RoleDuty6'
        ELSE 'RoleDuty7'
        END
) AS LastNonNullColumn
FROM (
    SELECT (
        CASE
            WHEN RoleDuty1 IS NULL THEN 'RoleDuty1'
            WHEN RoleDuty2 IS NULL THEN 'RoleDuty2'
            WHEN RoleDuty3 IS NULL THEN 'RoleDuty3'
            WHEN RoleDuty4 IS NULL THEN 'RoleDuty4'
            WHEN RoleDuty5 IS NULL THEN 'RoleDuty5'
            WHEN RoleDuty6 IS NULL THEN 'RoleDuty6'
            WHEN RoleDuty7 IS NULL THEN 'RoleDuty7'
            ELSE 'None'
        END
    ) AS FirstNullColumn
    FROM MyTable
)

Upvotes: 0

kjmerf
kjmerf

Reputation: 4345

I think you have to use a COALESCE with a bunch of CASE statements, like this:

SELECT COALESCE(rd1, rd2, rd3, rd4, rd5, rd6, rd7)
FROM
(SELECT
 CASE WHEN roleduty1 IS NOT NULL THEN 'roleduty1' ELSE NULL END AS rd1,
 CASE WHEN roleduty2 IS NOT NULL THEN 'roleduty2' ELSE NULL END AS rd2,
 ....
 FROM t) sub

Upvotes: 0

Hogan
Hogan

Reputation: 70523

This is exactly what the coalesce function is for:

SELECT COALESCE(RoleDuty1,RoleDuty2,RoleDuty3,RoleDuty4,RoleDuty5,RoleDuty6,RoleDuty7,'All null) as FIRST_NON_NULL
FROM aTable

Upvotes: 1

Related Questions