Prioritize one column over another

DB-Fiddle

CREATE TABLE logistics (
    id int primary key,
    campaign VARCHAR(255),
    quantity_offered VARCHAR(255),
    quantity_ordered VARCHAR(255), 
    quantity_delivered VARCHAR(255),
    quantity_recorded VARCHAR(255),
    quantity_completed VARCHAR(255)
);

INSERT INTO logistics
(id, campaign,
quantity_offered, quantity_ordered, quantity_delivered, 
quantity_recorded, quantity_completed
)
VALUES 
("1", "C001", "500", "450", "465", "462", "465"),
("2", "C002", "700", "570", NULL, NULL, NULL),
("3", "C003", "600", "610", "605", "602", NULL),
("4", "C004", "300", NULL, NULL, NULL, NULL),
("5", "C005", "400", "425", NULL, NULL, NULL),
("6", "C006", "900", "870", "868", NULL, NULL),
("7", "C007", "350", "360", "372", "375", "390"),
("8", "C008", "250", "290", NULL, NULL, NULL);

In the table above I have different campaigns with their corresponding quantities.
The quantities are filled in different columns.

Now, I want to get the latest available quantity for each campaign based on the following hierarchy:

quantity_completed > quantity_recorded > quantity_delivered > quantity_ordered > quantity_offered

The result should look like this:

Campaign      Quantity
C001            465
C002            570
C003            602
C004            300
C005            425
C006            870
C007            390
C008            290

What query do I need to achieve this?

Answer

Use coalesce():

select campaign, coalesce(quantity_completed, quantity_recorded, quantity_delivered, quantity_ordered, quantity_offered) as quantity
from logistics;

Advertisement

Answer