CODEWITHSUNDEEP
mysqlselect
Jimmyb
Jimmyb

Reputation: 890

MySQL Selecting First Non-Null Value from Multiple Rows

I need to select the first non-null value in a group of 2 rows. I have looked at many sites and answers, but haven't found a solution that meets my requirements. Here's my story:

CREATE TABLE `stacks` (
  `id` int(11) NOT NULL,
  `aisle` varchar(10) NOT NULL,
  `p1` int(11) DEFAULT NULL,
  `p2` int(11) DEFAULT NULL,
  `p3` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `stacks` (`id`, `aisle`, `p1`, `p2`, `p3`) VALUES
(1, 'Last', 20, 30, 40),
(2, 'One', NULL, 12, NULL),
(3, 'Two', 6, 9, NULL),
(4, 'Three', NULL, NULL, 18);

I am looking to find the quantity of items in a given row, but if this number is NULL I want to fall-back to the qty in Last. This should happen for each of the P columns separately. For example, let's say I want to find the quantity of items in aisle 3, if any of the items are NULL I want to select the value in last instead.

The result set for aisle three should look like:

aisle p1  p2  p3
Three 20  30  18

or the result for aisle two should be;

aisle p1  p2  p3
Two    6   9  30

What I've tried. I first tried selecting where p1, p2, or p3 isn't NULL, but this obviously won't work for multiple columns.

I then tried grouping, with one method attempting to separate NULL and NOT NULL values into separate columns so I could use coalesce. But I quickly found out that only the first row is selected regardless of the order.

SELECT aisle,     
    CASE WHEN `aisle` != 'last' THEN p1 END AS pa,
    CASE WHEN `aisle` = 'last' THEN p1 END AS pb
FROM `stacks`
WHERE aisle = 'three' OR aisle = 'last'
GROUP BY aisle
ORDER BY id DESC

I have looked into HAVING, multiple selects, unions, and even tried GROUP_CONCAT to order the values in a single field. This was probably my most successful as I was able to get the desired value listed first in the concatenated string, but I couldn't find a manageable way to isolate the proper value in MySQL.

Anyway, there appears to be methods such as first_value() not available in MySql, but I'm not convinced there is a way to do this in this version. I can't spend any more time banging my head against the wall, and hope you can set me straight one way or another.

Thanks!

Upvotes: 1

Views: 1859

Answers (2)

Barmar
Barmar

Reputation: 781210

Use a self-join to get the last value. Then use that as a fallback with IFNULL().

SELECT s.aisle, IFNULL(s.p1, l.p1) AS p1, IFNULL(s.p2, l.p2) AS p2, IFNULL(s.p3, l.p3) AS p3
FROM stacks AS s
CROSS JOIN stacks AS l
WHERE s.aisle != 'last' AND l.aisle = 'last'

Upvotes: 3

The Impaler
The Impaler

Reputation: 48810

I think you could do a JOIN and merge the rows. For example:

select
  s.id,
  s.aisle,
  coalesce(s.p1, l.p1) as p1,
  coalesce(s.p2, l.p2) as p2,
  coalesce(s.p3, l.p3) as p3
from stacks s
cross join stacks l
where l.aisle = 'Last'
  and s.aisle <> 'Last'

Upvotes: 2

Related Questions