MySQL how to select greatest of two column 0 and null

Question

the table data like this, how to compare 0 is greater than null, and get the greatest value ?

id	score_a	score_b
1	0	null
2	null	null
3	1	0

I want the result like this

id	max_score
1	0
2	null
3	1

I tired this, but the result is unexpected

SELECT greatest(coalesce(score_a,0),coalesce(score_b,0)) as max_score FROM table

id	max_score
1	0
2	0
3	1

@RiggsFolly i want compare 0 > null, so the result should be 0, null, 1 — kenken9999, Mar 06 '21 at 15:55
Does this answer your question? [MySQL comparison with null value](https://stackoverflow.com/questions/9608639/mysql-comparison-with-null-value) — Luuk, Mar 06 '21 at 16:05

score 0 · Accepted Answer · answered Mar 06 '21 at 16:01

0

Use a CASE expression:

SELECT CASE 
         WHEN COALESCE(score_a, score_b) IS NULL THEN NULL
         ELSE GREATEST(COALESCE(score_a, 0), COALESCE(score_b, 0)) 
       END max_score 
FROM tablename

See the demo.
Results:

max_score
0
null
1

answered Mar 06 '21 at 16:01

forpas

160,666
10
38
76

work perfect ;), last question, will this cause bad performance ? because in the table has 3m records – kenken9999 Mar 06 '21 at 16:07
@kenken9999 there is no WHERE clause to filter the rows because you want all of them, so the performance depends on on the number of rows. – forpas Mar 06 '21 at 16:23
yes, no where clause, got it, thankyou for your reply ;) – kenken9999 Mar 06 '21 at 16:26

MySQL how to select greatest of two column 0 and null

1 Answers1