Check equality of n numbers in mysql

Question

I need to check whether all numbers are the same. The values are coming from different columns. The signiture should allow to put any amount of columns (like the COALESCE(...) method)

SELECT equality(42, 42, 42)

should return true and

SELECT equality(23, 42, 133)

should return false.

Is there a nice way to code this?

At time i did it like this:

SELECT (x1 = x2 AND x2 = x3);

But i hope there is a more elegant way.

@njk - Unless I'm missing something, we don't have two queries here. — Álvaro González, Dec 03 '12 at 15:24

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

4

Use this:

SELECT GREATEST(42, 42, 42) = LEAST(42, 42, 42)

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 03 '12 at 15:25

Praveen Kumar Purushothaman

164,888
24
203
252

Nopes. Check out different values. – Praveen Kumar Purushothaman Dec 03 '12 at 15:27
1

He means `SELECT GREATEST(42, 42, 42) = LEAST(42, 42, 42)` – Raphaël Agneau de Selve Dec 03 '12 at 15:27
@RaphaëlAgneaudeSelve Thanks. It was my mistake. Updated it. – Praveen Kumar Purushothaman Dec 03 '12 at 15:28
@ÁlvaroG.Vicario Yeah buddy, sorry. Typo there. So, it checks for both greater and lesser value in the set to be equal. – Praveen Kumar Purushothaman Dec 03 '12 at 15:28
1

+1 Great insight here. I think the only failure might be if one also wishes to return true if all values are `NULL` where even the [`NULL`-safe equality operator `<=>`](http://dev.mysql.com/doc/en/comparison-operators.html#operator_equal-to) won't help because it would also return true if the set contains at least one `NULL` and at least one non-`NULL`. I suspect the solution in such a case would be `SELECT COALESCE(GREATEST(42,42,42), 42,42,42) <=> LEAST(42,42,42)`. – eggyal Dec 03 '12 at 15:40

Check equality of n numbers in mysql

1 Answers1

Use this: