MySQL Independently select the last value for multiple columns that is not null
Here's an example dataset I'm dealing with:
+----+-----+-----+-----+-----+
| id | a | b | c | d |
+----+-----+-----+-----+-----+
| 1 | 1 | | | |
| 2 | | 2 | | |
| 3 | | | | |
| 4 | | | | 4 |
| 5 | | 3 | | |
+----+-----+-----+-----+-----+
I want to select the lowest values. If this value has never been set then I need "null" otherwise I want the lowest result. In this case, I need a result set:
+-----+-----+-----+-----+
| a | b | c | d |
+-----+-----+-----+-----+
| 1 | 3 | | 4 |
+-----+-----+-----+-----+
I've tried queries like options:
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;
It didn't work.
Do I have to run queries for each value separately or is there a way to do this in just one query?
+3
source to share
1 answer
If you are ok with changing the type to 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;
Notes:
- An intermediate view is needed because you need to order entry into
GROUP_CONCAT
. - After compressing the rows with
GROUP_CONCAT
(using the default comma separator), we clear the first column withsubstring_index
.substring_index
to NULL returns NULL if required. - If you want result columns to be INT, you will need to drop each column one more time.
+3
source to share