CODEWITHSUNDEEP
mysqlselect
ieatpizza
ieatpizza

Reputation: 484

MySQL Independently select last value across multiple columns which isn't null

Here's an example dataset that I'm dealing with:

+----+-----+-----+-----+-----+
| id |  a  |  b  |  c  |  d  |
+----+-----+-----+-----+-----+
|  1 |  1  |     |     |     |
|  2 |     |  2  |     |     |
|  3 |     |     |     |     |
|  4 |     |     |     |  4  |
|  5 |     |  3  |     |     |
+----+-----+-----+-----+-----+

I want to select the bottom-most values. If this value has never been set, then I'd want "null", otherwise, I want the bottom-most result. In this case, I'd want the resultset:

+-----+-----+-----+-----+
|  a  |  b  |  c  |  d  |
+-----+-----+-----+-----+
|  1  |  3  |     |  4  |
+-----+-----+-----+-----+

I tried queries such as variations of:

SELECT DISTINCT `a`,`b`,`c`,`d`
FROM `test`
WHERE `a` IS NOT NULL
AND `b` IS NOT NULL
AND `c` IS NOT NULL
AND `d` IS NOT NULL
ORDER BY 'id' DESC LIMIT 1;

This didn't work.

Would I have to run queries for each value individually, or is there a way to do it within just that one query?

Upvotes: 3

Views: 831

Answers (1)

StuartLC
StuartLC

Reputation: 107277

If you are OK with changing type to a char, you can do this:

SELECT substring_index(GROUP_CONCAT(a),',',1) as LastA, 
       substring_index(GROUP_CONCAT(b),',',1) as LastB, 
       substring_index(GROUP_CONCAT(c),',',1) as LastC, 
       substring_index(GROUP_CONCAT(d),',',1) as LastD
FROM
(
  SELECT id, a, b, c, d
  FROM MyTable
  ORDER BY id DESC
) x;

SqlFiddle here

Notes:

  • The intermediate derived table is needed as the input to GROUP_CONCAT needs to be ordered.
  • After compressing the rows with GROUP_CONCAT (using the default comma delimiter), we then scrape out the first column with substring_index. substring_index on NULL returns NULL, as required.
  • If you need the resultant columns to be INT, you'll need to cast each column again.

Upvotes: 3

Related Questions